Forum Discussion
Filter Context in a calculation group
- 4 years ago
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.
Greg_Deckler It's the same as my separate visual code. I hoped it would work as it was.
Alisea_MI Hmm, normally I would expect: CALCULATE(SELECTEDMEASURE(), ALLEXCEPT(Customer,Customer[Sales Region]))
Calculation groups in Analysis Services tabular models | Microsoft Docs
- Alisea_MI4 years ago
Resolver II
Greg_DecklerI have tried it as well - it returns the same result.
- Greg_Deckler4 years ago
Community Champion
Alisea_MI What is the formula for growth quantity? Can you post mock data to emulate this?
- Alisea_MI4 years ago
Resolver II
Greg_Deckler I have created some example file, that replicates the issues on page "Details". What I expect in the calculation group is this case is what I see in the separate visual for Region, 6%.
The only way I managed to have to work is by turning the "Keep all filters on".Howeverm in this case I get another issue with the Period slicer. My user wants to be able to use it on the details page as well. And in the case when I have all the filters on , the slicer on the detail page is set to when is chosen on the 1st page. I have tried to change advanced settings on the slicer syns, however it hasnt worked.
Do you have any tips?
Thank you in advance!Alisea