Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
frodriguezw
Frequent Visitor

Calculate Measure gets different results depending on filter

Hi,

 

I'm new to this forum, so I really appreciate your help here!

 

I downloaded Sales and Marketing Sample PBIX which contains VanArsdel Sales. I have the following table:

 

Captura.PNG

 

This is the Product Table and I have a Conditional Calculated Column names isVanArsdel in which it says Yes if the product is from VanArsdel as Manufacterer and No otherwise.

 

This column is connected to SalesFact Table through ProductID, which is unique ID in ProductTable (so each Product will have only one manufacturer).

 

If I try to calculate VanArsdel Unit Sales for a specific year, I created 2 different measurements:

 

Measure 1:

CALCULATE([Total Units], FILTER(ALL('Product'[isVanArsdel]), 'Product'[isVanArsdel]="Yes"))

Measure 2:

CALCULATE([Total Units], FILTER(ALL('Product'[Manufacturer]), 'Product'[Manufacturer]="VanArsdel"))

[Total Units] is just SUM(SalesFact[Units])

 

So far so good. If I look at the Multi-row card I get the same number (297,606). The problem is when I start using this measures in other calculated measures. For some reason, measurement 2 is giving numbers to all of manufacturers, not just VanArsdel as I had expected, so if then I want to measure VanArsdel Market share for example, and select a different manufacturer with a slicer, instead of getting 0%  in VanArsdel's market share I get values using measurement 2, but this doesn't happens when I use measurement 1.

 

So to look for differences, I created a matrix in which I looked at the manufacturer and both measurements. There I found the difference:

 

Captura2.PNG

 

What I find is odd is that in both measurements, FILTER should apply to the exact same rows, so I don't get why I get different results.

 

Can someone help me in trying to explain this?

 

If it can help to understand this confusion, if I put both variables together with the isVanArsdel as column in a matrix I obtain the following matrix:

 

Captura3.PNG

 

Again, everything is fine until the totals, so when I filter by Manufacturer, the second measure will still show as values all of VanArsdel totals.

 

I appreciate any help!

 

Greetings,

1 ACCEPTED SOLUTION
Arentir
Resolver III
Resolver III

Hi @frodriguezw

Welcome to the wonderful world of DAX!

 

It is an interesting one. What you see is all due to filter context.

I won't go into the detail of evaluation context, if you are not too familiar with this I strongly suggest that you look into the ressources I am putting at the bottom of this post.

 

Calculate is a function that modify the filter context. In your matrix, your filter context is Manufacturer, ie each row should compute the total unit of Abbas, Aliqui, etc...

But calculate changes the rule of the game and discards your natural filter context:

Measure 1: You have a filter context on Manufacturer (table row), but your calculate will only computes when IsVanArsdel = "Yes". Which is true only for Manufacturer = VanArsdel. Conclusion: you have no value but for this row

 

Measure 2: You have a filter context on Manufacturer (table row), but your calculate does too as this as you have specify this very column ("ALL('Product')[Manufacturer])). Therefore it will ignore your table row completely and give you the result of your condition for any manufacturer on rows. Conclusion: your table row is ignored and the value for VanArsdel is repeated for each row.

 

For more on Evaluation context ( Filter Context + Row Context):

A true master of DAX and great introduction

https://www.youtube.com/watch?v=klQAZLr5vxA (from 01:15:00)

https://www.youtube.com/watch?v=6ncHnWMEdic&t=2403s (the whole thing)

 

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

 

I need to calculate the sum of VCI_RECEIVED_Head on group by feedyard ,pen_id and lot_id .can you help me here . even if the rationid is different but for example in below row2 and row3 are unique on mentioned above 3 columns so in the calcuation only 123 should be considered . total result should be =118+123+129+166+250 .Hope you got it

 

 calculate.jpg

Hi! I just looked at this.

 

I don't know if is the best answer, but it worked for me!

 

Create column

KEY_ID =[FEEDYARD]&[PEN_ID]&[LOT_ID]

(This will give you the unique key for the parameters you are looking for)

 

 

Then create Measure DISTINCT_AVERAGE

 

DISTINCT_AVERAGE:=AVERAGEX(SUMMARIZE('Table',[KEY_ID],[VCI_RECEIVED_Head]), [VCI_RECEIVED_Head])

(This will average the amounts for the key you are looking for)

 

 

Finally create the DISTINCT_SUM measure:

 

 

DISTINCT_SUM:=SUMX(DISTINCT('Table'[KEY_ID]),[DISTINCT_AVERAGE])

You will get the following table:

 

 

Captura.PNG

 

Hope this help you!

 

 

Likewise, if you don't want to create another column, this will also work:

 

DISTINCT_AVERAGE = AVERAGEX(SUMMARIZE('Table',[FEEDYARD],[LOT_ID],[PEN_ID],[VCI_RECEIVED_Head]), [VCI_RECEIVED_Head])

DISTINCT_SUM = SUMX(SUMMARIZE('Table',[FEEDYARD],[LOT_ID],[PEN_ID],[VCI_RECEIVED_Head]),[DISTINCT_AVERAGE])

It will give the same results.

 

Hope this could help! I'm new at DAX so maybe there is a better way of doing this.

 

 

Greetings,

Arentir
Resolver III
Resolver III

Hi @frodriguezw

Welcome to the wonderful world of DAX!

 

It is an interesting one. What you see is all due to filter context.

I won't go into the detail of evaluation context, if you are not too familiar with this I strongly suggest that you look into the ressources I am putting at the bottom of this post.

 

Calculate is a function that modify the filter context. In your matrix, your filter context is Manufacturer, ie each row should compute the total unit of Abbas, Aliqui, etc...

But calculate changes the rule of the game and discards your natural filter context:

Measure 1: You have a filter context on Manufacturer (table row), but your calculate will only computes when IsVanArsdel = "Yes". Which is true only for Manufacturer = VanArsdel. Conclusion: you have no value but for this row

 

Measure 2: You have a filter context on Manufacturer (table row), but your calculate does too as this as you have specify this very column ("ALL('Product')[Manufacturer])). Therefore it will ignore your table row completely and give you the result of your condition for any manufacturer on rows. Conclusion: your table row is ignored and the value for VanArsdel is repeated for each row.

 

For more on Evaluation context ( Filter Context + Row Context):

A true master of DAX and great introduction

https://www.youtube.com/watch?v=klQAZLr5vxA (from 01:15:00)

https://www.youtube.com/watch?v=6ncHnWMEdic&t=2403s (the whole thing)

 

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

 

Thank you very much for clarifying this!

 

I will have to take extra careful in the filters I'm applying!

 

Greetings,

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors