Can the function "TOPN" be used as a filter?

[atkach123]

Hi,

I'm currently trying to use TOPN as a filter context in a formula. Essentially, without going into too much detail, I'm trying to get a driver's (worker's etc.) overtime rate to be applied to his hours for the week. The driver's overtime rate for the week should be the rate on the last day of the week. In the simplified example below, that rate should be $16.

However, with a full and much larger data set, my thoughts to pull that overtime rate were something along the lines of: =CALCULATE([Overtime rate], filter by Driver, filter by week, TOPN(1,table, [Date])). With the TOPN function, I'm trying to get the formula to filter for the maximum (i.e. last) date of that week so that it can pull in that respective overtime wage.


Date Driver Overtime rate
8/31/2015 A 14
9/1/2015 A 14
9/2/2015 A 14.5
9/3/2015 A 14.5
9/4/2015 A 15
9/5/2015 A 16


Thanks!

 

[atkach123]

Date Driver Overtime rate
8/31/2015 A 14
9/1/2015 A 14
9/2/2015 A 14.5
9/3/2015 A 14.5
9/4/2015 A 15
9/5/2015 A 16

 

[FDibbins]

As far as I know, CALCULATE() and TOPN() are not native functions?

This will give you the rate on Saturday - the last date in your sample...
[Table="width:, class:grid"][tr][td] [/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]Date[/td][td]Driver[/td][td]OT Rate[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]8/31/2015[/td][td]A[/td][td]

14​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]9/1/2015[/td][td]A[/td][td]

14​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]9/2/2015[/td][td]A[/td][td]

14.5​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]9/3/2015[/td][td]A[/td][td]

14.5​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]9/4/2015[/td][td]A[/td][td]

15​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]9/5/2015[/td][td]A[/td][td]

16​

[/td][td]

16​

[/td][/tr]
[/table]
I2=IF(WEEKDAY(F2,2)=6,H2,"")
copied down

 

[shg]

TOPN is a DAX function, Ford.

 

[FDibbins]

Thanks shg, I kinda had a feeling that was the case

 

[atkach123]

Hey guys,

Thanks for the response. Yes, sorry, should have mentioned this will be done in power pivot. The example above is just a simplified version of the huge amount of data that I have.


Thanks!

 

[Ozeroth]

Hi there,

Yes, since TOPN returns a table, it can be used to generate a 'maximum' filter argument for CALCULATE.
There are other options: the most concise (for a maximum date) would be LASTDATE, and more generally you can use LASTNONBLANK.

So the options would be:

=CALCULATE( [B][Your measure][/B]<your measure="">,..., LASTDATE( table[Date] ) )
=CALCULATE( <your measure="">[B][Your measure][/B],..., TOPN( 1, table table[Date] ) )
=CALCULATE( <your measure="">[B][Your measure][/B],..., LASTNONBLANK( VALUES( table[Date] ) ,1 ) )

How you handle the Driver/Week filters would depend on how you are using this measure in your model.
Also, if you need this measure to work when the filter context is on a date other than the last day of the week you would need to handle that e.g. by clearing the Date filter but retaining the Week filter.

Ozeroth </your></your></your>

 

[atkach123]

LASTDATE embedded in CALCULATE worked like a charm - thanks Ozeroth and everyone else who responded!


Cheers,

Adam