cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alisea_MI
Resolver I
Resolver I

Filter 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).

2.jpg

 

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:

4.jpg

 

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).

 

1.jpg

 

3.jpg

 

 

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!

1 ACCEPTED SOLUTION
Alisea_MI
Resolver I
Resolver I

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.

View solution in original post

7 REPLIES 7
Alisea_MI
Resolver I
Resolver I

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
Super User
Super User

@Alisea_MI So what is your Calculation Group DAX code?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler It's the same as my separate visual code.  I hoped it would work as it was.

4.jpg

@Alisea_MI Hmm, normally I would expect: CALCULATE(SELECTEDMEASURE(), ALLEXCEPT(Customer,Customer[Sales Region]))

 

Calculation groups in Analysis Services tabular models | Microsoft Docs


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerI have tried it as well - it returns the same result.

5.jpg6.jpg

@Alisea_MI What is the formula for growth quantity? Can you post mock data to emulate this?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.