topic Re: Calculate doesn't work correctly - FILTER (beginner question) in DAX Commands and Tips

Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:06:09 GMT

Hi!

I have beginner question, maybe I don't understand something in correct way.

I have to calculate count of products above average price.

I have tried to calculate that in this way:

avgprice = average(Products[UnitPrice])

countofproducts = calculate(count(Products[ProductID]),filter(Products,Product[UnitPrice]>avgprice))

solution above gives me blank and I don't understand why

when I calculate it that way it works correctly:

countofproducts2 = calculate(count(Products[ProductID]),filter(Products,Product[UnitPrice]>average(Products[UnitPrice])

So why cannot I calculate it in the first way? Even if I put calculate(average(Products[UnitPrice])) it doesn't work correctly 😞

could you explain it to me?

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:21:26 GMT

Hello please try

calculate(count(Products[ProductID]),Maxx(products,Product[UnitPrice]>avgprice))

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:29:32 GMT

Thanks, but I'm not asking for solution, I'm asking for explanation why my solution didn't work

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:30:47 GMT

Hi, maybe you forgot a letter s in the filter of the calculate. You wrote Product[UnitPrice] instead Products[UnitPrice].

avgprice = average(Products[UnitPrice])

countofproducts = calculate(count(Products[ProductID]),filter(Products,Product[UnitPrice]>avgprice))

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:33:32 GMT

No, its not working, this is not the case, I have checked it twice

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:40:08 GMT

Ok, i dont know if in countofproducts is necesary the filter:

Maybe that is the problem, i would use the following formula:

countofproducts = calculate(count(Products[ProductID]),Product[UnitPrice]>avgprice))

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 11:40:34 GMT

When you use a measure in DAX, the DAX will wrap the measure in a CALCULATE. CALCULATE transforms the current row context into a filter context.

The FILTER function introduces a row context. This is important. The FILTER function iterates through the table used as the first parameter row by row and tests the expression used as the 2nd argument. If you use a measure in the expression, the measure will be wrapped in a CALCULATE and the current row context introduced by FILTER will be turned into a filter context and passed to the measure. This means that the measure will always evaluate in a filter context defined by the current row context of the FILTER function.

The reason why setting the measure to a variable bypasses this is that the value associated with the measure outside of any row context is saved to the variable and so it will stay constant and be uninfluenced by row context (if using the FILTER function). It also often allows you to bypass the need for the FILTER function in a CALCULATE to begin with as the expression in a CALCULATE has some drawbacks

so try:

Filter desired = VAR avgprice = average(Products[UnitPrice]) Return calculate(count(Products[ProductID]),filter(Products,Product[UnitPrice]>avgprice))

Re: Calculate doesn't work correctly - FILTER (beginner question)

Anonymous — Sat, 29 Oct 2022 12:11:11 GMT

Great answer, thank you buddy! So, if I understood correctly, I cannot use measure when comparing in FILTER function because measure which has filter context will evaluate into row context in every row (when iterating with filter)? So the constant value in measure avgprice will always be evaluated into value based in filter context which comes from row context?