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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Koushikrish
Helper I
Helper I

DAX Calculation doesn't work when collapsing groups

I have a composite model that is connected to (3) remote models (shared datasets). 

As part of one report, the Rows on the report matrix need to have a custom sorting based on a 'Wamp Name' column, so we created a Custom sort local table to handle that.

 

Koushikrish_0-1634660878759.png

Each Wamp contains a 'Brand Group' and in the report heirarchy the brand groups are grouped under each 'Wamp Name'. Hence the report appears like the screenshot below. The last 'NRperHLDeltaYrlyAvg' measure column is the difference between the 1st and 2nd measure columns.

 

Koushikrish_1-1634661038276.png

 

The Brand Table has a relationship to the local sort order table based on the 'Wamp Name' so that the sorting is based on the Wamp Order from the local table. 

Koushikrish_2-1634661228568.png

 

Now, this all works fine when all the 'Wamp Name' groups are expanded on the group. The problem occurs when one of these groups is collapsed.

 

When all groups are expanded:

'NRperHLDeltatoYrlyAvg' calculation works correct

 

Koushikrish_3-1634661394854.png

 

When one of the groups is collapsed (here Premium group is collapsed):

'NRperHLDeltatoYrlyAvg' calculation doesn't work. All values are 0.

 

 

Koushikrish_4-1634661541572.png

 

The interesting part is that the first 2 base measures seem to be showing up the correct values when expanded or collapsed but it doesn't work that way for the 3rd measure calculated from these 2. We have tried creating different calculations to get around this on the local measures table but nothing seems to work when a level on the 'Wamp Name' is collapsed.

 

Can anyone advise a way to get around this? Any ideas appreciated!

 

 

 

 

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @Koushikrish 

 

You can check the document: Use composite models in Power BI Desktop - Power BI | Microsoft Docs

It mentions:

vjaneygmsft_2-1634884407622.png

 

From your screenshot I can see that they are composite models. So I think this is the cause of the problem.

 

vjaneygmsft_1-1634884307220.png


Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
Community Support

Hi, @Koushikrish 

 

You can check the document: Use composite models in Power BI Desktop - Power BI | Microsoft Docs

It mentions:

vjaneygmsft_2-1634884407622.png

 

From your screenshot I can see that they are composite models. So I think this is the cause of the problem.

 

vjaneygmsft_1-1634884307220.png


Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

Thank you for the response!

 

I still do not understand how the 1st and 2nd measures work in the same context considering they are DAX calculations as well:

Koushikrish_0-1634919320480.png

 

Its only the 3rd measure which is the difference between the 1st and 2nd that don't seem to work properly. Any idea on this?

Hello @Koushikrish 

 

If the result is incorrect, it may be a problem with the measure. Can you share the code of measure and more details about column? Only these screenshots are hard to judge.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.


Best Regards,

Community Support Team _ Janey

Sorry for the delay in response. We were able to figure out a solution from your response here above.

Koushikrish_0-1635438351709.png

 

Instead of incorporating the WAMP Sort Order on a separate local table, we included it as part of the 'MACO-Brand' table itself on the Remote model (the one as DirectQuery on the Composite model). Once that was done, we used that Sort Order field instead for the sorting and it seems to have fixed our initial problem. Thanks for your help on this!!

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors