date measure as a filter in Calculate function

[zeitssParticipant Started: 1Replies: 3Total: 4]

hi there, it appears i can’t use a date measure as a filter in the Calculate function (Excel 2010, PP v 11.0.3), please note the following:

this works:  CurrentCommTest:=CALCULATE([pd Commission],dsComm[Commission Month]=date(2018,1,1))

this works:  CurrDate:=date(2018,1,1)

this doesn’t work:  CurrentCommTest:=CALCULATE([pd Commission],dsComm[Commission Month]=[CurrDate])

i get a semantic error saying calculate has been used in a true/false expression.  i was able to do a work around, but i would think this should work, and seems strait forward to me.  i’m having issues working with dates like this.  any suggestions or good articles that would explain dates and filters, would be much appreciated.

thanks!

[tomallanKeymaster Started: 0Replies: 417Total: 417]

zeitss,

Thanks for reaching out with your question!

The answer here is that the boolean filter syntax of CALCULATE was never meant to work with measures (nor with a single expression that attempt to apply a filter on more than one column).

Why? Because the boolean filter syntax is just a shortcut (“syntax sugar”) that the DAX engine uses to fit into a template that applies the FILTER function against a single column.

Please consider the following instead:

CurrentCommTest :=
CALCULATE (
    [pd Commission],
    FILTER ( ALL ( dsComm ), dsComm[Commission Month] = [CurrDate] )
)

[tomallanKeymaster Started: 0Replies: 417Total: 417]

zeitss,

Thanks for reaching out with your question!

The answer here is that the boolean filter syntax of CALCULATE was never meant to work with measures (nor with a single expression that attempts to apply a filter on more than one column).

Why? Because the boolean filter syntax is just a shortcut (“syntax sugar”) that the DAX engine uses to fit into a template that applies the FILTER function against a single column.

Please consider the following instead:

CurrentCommTest :=
CALCULATE (
    [pd Commission],
    FILTER ( ALL ( dsComm ), dsComm[Commission Month] = [CurrDate] )
)

[zeitssParticipant Started: 1Replies: 3Total: 4]

that worked perfectly!  thank you tomallan and thanks for the explanation.

[zeitssParticipant Started: 1Replies: 3Total: 4]

sorry tomallan, i did not give you the whole story.  i’m actually trying to pull the current date from the data source, and not hard code it.  i hard coded the date above to explain my issue, not realizing i would still have an issue.  your solution works for what i gave, but consider the following formula:

Current Commission:=
CALCULATE(
[pd Commission],
FILTER(ALL(dsComm),dsComm[Commission Month]=[Curr Comm Month])
)

this formula works when:
Curr Comm Month:=date(2018,1,1)  i get just January’s results

this formula doesn’t work when:
Curr Comm Month:=max(dsComm[Commission Month])  i get the total result of dataset instead of result for just January

but both ways appear to produce the exact same output, a January date in date time format.  can i not pull the date from the data source and use it in my formulas?

thanks and sorry for not giving the complete problem.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Try below and let me know the results:

Curr Comm Month:=MAXX (ALL ( dsComm[Commission Month] ), dsComm[Commission Month] )

If that works for you, I will explain. If not, I will ask for some sample data.

Tom

[zeitssParticipant Started: 1Replies: 3Total: 4]

YES!  this gives me the result expected.  wow, so much to simply get the most current date from data source and use it in a formula.  🙂

[tomallanKeymaster Started: 0Replies: 417Total: 417]

zeitss,

The reason my formula functioned correctly is that it was designed with filter context in mind. Filter context is the filtering effect in Pivots coming from pivot Rows, Columns, Filter and slicers.

You were getting the total because the expression:

Curr Comm Month:=max(dsComm[Commission Month])

was re-evaluated in every row of the pivot (not always equal to the maximum commission month for all rows in the table).

Tom

[Author]

Posts