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.
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.
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.
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
When one of the groups is collapsed (here Premium group is collapsed):
'NRperHLDeltatoYrlyAvg' calculation doesn't work. All values are 0.
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!
Hi, @Koushikrish
You can check the document: Use composite models in Power BI Desktop - Power BI | Microsoft Docs
It mentions:
From your screenshot I can see that they are composite models. So I think this is the cause of the problem.
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
Hi, @Koushikrish
You can check the document: Use composite models in Power BI Desktop - Power BI | Microsoft Docs
It mentions:
From your screenshot I can see that they are composite models. So I think this is the cause of the problem.
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:
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?
Sorry for the delay in response. We were able to figure out a solution from your response here above.
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!!
