Forum Discussion
Resolver IIFilter Context in a calculation group
Hi!
I have been struggling with the issue below for several days now, and would really appreciate some expert input.
I have created a calculation group to show several calculated measure side by side in a visual.
My report has a drill through for navigation purposes (keep all filters turned off to able to use slicers on the last page otherwise they only show the choice made on page 1) between the following dashboards: Salesperson - > City-> Store-> Store Details.
On the "Store Details" page I am displaying 1) the growth of the store I have drilled to, 2)the growth of the whole region the store belongs to.
My issues is that a calculated measure loses filter context when used in a calculated group. However the same measure (with the same formula) works correctly when visualized in a separate chart side by side with the calculation group visual. Filters applied affecting these visuals are the same. The drill-through filer on the page shows Sales Region (All).
Please see an issue example below.
The formula for my measure shows a growth for a chosen period for a region.
Growth QuantityRegion = CALCULATE([Growth Quantity], ALLEXCEPT(Customer,Customer[Sales Region]))
The calculated group dax code for the region is the same:
The correct region should by picked by the context since we have drilled us to the specific store.
In a separate visual, this formula shows the correct region with the correct data (please see 1 and the table in the pictures below).
In a calculated measure, this formula shows the total for all the regions (please see the 2 and the table in the pictures below).
How can I pass the region context to the calculated group? Or is calculated group meant only to show the grand total row?
THe links for a mock download that replicates the issues are here:
https://www.dropbox.com/s/g7u7kwkyf96fthq/Community.pbix?dl=0
https://1drv.ms/u/s!AsRPYONOO3kumErdiECMBpBNrTVr?e=WyJNv3
Thank you so much in advance for all the input on this!
I have been working on this for a while and would like to share some of the findings and a solution in case anyone else would need to do the same.
I have two calculated group solutions: 1) one where I have drill-through page, 2) one where I only have one page with the filters on this page.
My calculation group in located on a detail pagek which shows details for a certain store we have drilled us to. THe calculation group shows growth for: store, city, chain, region and country.
In order to make the figures work, I needed to have drill-through pages for a sales person, city, chain and store. Note: if you skip any of these, e.g. Chain, the Chain figues on the detail page would not be correct.
The formulas I used in the calculated group were:
-Store:
CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALL(Customer[Sales Region]), ALLSELECTED(Customer[Store City]), ALL(Customer[Chain Store]), ALL(Customer[Store Name]))
-Chain:
CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALL(Customer[Sales Region]), ALL(Customer[Store City]), allselected(Customer[Chain Store]), all(Customer[Store Name]))
-Region:
CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALLSELECTED(Customer[Sales Region]), ALL(Customer[Store City]), ALL(Customer[Chain Store]), ALL(Customer[Store Name]))
- Country:
CALCULATE(SELECTEDMEASURE(), all(customer))
The order of elements in the formula makes difference.
7 Replies
- Greg_Deckler
Community Champion
Alisea_MI So what is your Calculation Group DAX code?
Alisea_MIGreg_Deckler It's the same as my separate visual code. I hoped it would work as it was.
- Greg_Deckler
Alisea_MI Hmm, normally I would expect: CALCULATE(SELECTEDMEASURE(), ALLEXCEPT(Customer,Customer[Sales Region]))
Calculation groups in Analysis Services tabular models | Microsoft Docs
- Alisea_MI
I have been working on this for a while and would like to share some of the findings and a solution in case anyone else would need to do the same.
I have two calculated group solutions: 1) one where I have drill-through page, 2) one where I only have one page with the filters on this page.
My calculation group in located on a detail pagek which shows details for a certain store we have drilled us to. THe calculation group shows growth for: store, city, chain, region and country.
In order to make the figures work, I needed to have drill-through pages for a sales person, city, chain and store. Note: if you skip any of these, e.g. Chain, the Chain figues on the detail page would not be correct.
The formulas I used in the calculated group were:
-Store:
CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALL(Customer[Sales Region]), ALLSELECTED(Customer[Store City]), ALL(Customer[Chain Store]), ALL(Customer[Store Name]))
-Chain:
CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALL(Customer[Sales Region]), ALL(Customer[Store City]), allselected(Customer[Chain Store]), all(Customer[Store Name]))
-Region:
CALCULATE(SELECTEDMEASURE(), ALL(Customer[Sales Person]), ALLSELECTED(Customer[Sales Region]), ALL(Customer[Store City]), ALL(Customer[Chain Store]), ALL(Customer[Store Name]))
- Country:
CALCULATE(SELECTEDMEASURE(), all(customer))
The order of elements in the formula makes difference.
