cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge. Anonymous
Not applicable

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

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?

1 ACCEPTED SOLUTION  Impactful Individual

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))``````
7 REPLIES 7  Helper V

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)) Anonymous
Not applicable

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

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))`````` Anonymous
Not applicable

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?  Helper V

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))  Impactful Individual

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

Thanks, but I'm not asking for solution, I'm asking for explanation why my solution didn't work Announcements #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (3,855)