help in understanding use of FILTER within CALCULATE

[mkins]

hi there,

I am looking to understand why a the dax formula I have used has returned the correct answer by me by using the FILTER function within CALCULATE and why the answer is incorrect when I use the <filter> arguement within the CALCULATE expression.

This was the original formula is used:

CALCULATE(Countrows(Query1),

Query1[Service] = "build",

Query1[Name] = Query1[Winner])

This did not give me the correct result.

However, when I wrapped FILTER around the second filter arguement in the CALCULATE function, it did work:

CALCULATE(Countrows(Query1),

Query1[Service] = "build",

FILTER(Query1,Query1[Name] = Query1[Winner])).

I understand that the FILTER function ultimately returns a table, however can't get my head around why it hasn't worked within the <filter> arguement within the CALCULATE function.

Any assistance in helping me understand this would be much appreciated.

TIA.

[FreemanZ · Accepted solution]

hi @mkins 

CALCULATE(Countrows(Query1),

Query1[Service] = "build")

is the syntax sugar of 

CALCULATE(Countrows(Query1),

FILTER(ALL(Query1[Service]), Query1[Service] = "build"))

syntax sugar for CALCULATE+FILTER only works when a column is comparing with constant values, like "build". In case of comparing with another column or a measure, full expression wrapped with filter is needed.

[mkins]

Many thanks for this reply....i think I understand what you mean. 

If the column were to have constant values (eg every value is player 1 column would be the same value) would this be considered as a constant and therefore still work? 

[FreemanZ]

hi @mkins 

could you elaborate this:

every value is player 1 column would be the same value

?

[mkins]

Hi @FreemanZ 

Basically, in my dataset there is a column that has the same value repeated for every row (for the player I am interested in, and there is also another column with the opponents name in every row)

I have been reading about online and it appears that the issue I was encountering was due the part of the formula where one column value was equal to another column value:

Query1[Name] = Query1[Winner]

When used like this any slicers applied would not work. However, when I changed this to FILTER(Query1,Query1[Name] = Query1[Winner] it did work.