Home › Forums › Power Pivot › date measure as a filter in Calculate function
This topic contains 7 replies, has 2 voices, and was last updated by tomallan 6 years, 2 months ago.
-
AuthorPosts
-
February 14, 2018 at 10:49 pm #9213
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!
February 15, 2018 at 12:57 am #9214zeitss,
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] ) )
February 15, 2018 at 12:59 am #9215zeitss,
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] ) )
February 15, 2018 at 5:28 pm #9227that worked perfectly! thank you tomallan and thanks for the explanation.
February 15, 2018 at 7:06 pm #9228sorry 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 resultsthis formula doesn’t work when:
Curr Comm Month:=max(dsComm[Commission Month]) i get the total result of dataset instead of result for just Januarybut 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.
February 16, 2018 at 6:08 am #9229Try 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
February 16, 2018 at 7:31 pm #9238YES! 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. 🙂
February 16, 2018 at 9:26 pm #9242zeitss,
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
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.