Dynamic Legend Switch: Toggling between Detailed and Aggregated views without Bookmarks

[AlonCarmel]

Hi everyone,

I’m trying to implement a toggle in my Power BI report that allows users to switch the granularity of a Stacked Column Chart's Legend. I want to avoid using Bookmarks because I have several other slicers on the page and want to maintain the state.

The Scenario: I have a financial report showing "Changes in CSM".

1. Detailed View: Should show 8 categories (Lapses, Mortality, Expenses, etc.).

2. Summary View: Should group those 8 into only 3 categories: "Other", "Other estimates", and "Change in estimate" (which is a sum of the remaining 5).

What I’ve tried: I created a separate DATATABLE for the Summary view and a Measure using SWITCH to aggregate the values. I then tried to use Field Parameters to switch between the parameters column of the Original Table and the parameters column of the Summary Table.

The Issue: When I use the Field Parameter in the Legend and the Slicer, the visual either goes blank or the slicer doesn't filter the chart correctly. It seems like the relationship between the Field Parameter and the underlying measures is breaking.

Current Setup:

• Detailed Table (Changes in estimate): Contains 8 rows of parameter names.

• Summary Table (Changes in estimate1): Contains 3 rows.

• The Aggregation Measure:
  Changes in estimates CSM parameter =
  VAR Numerator =
  SUMX(
  VALUES( 'Changes in estimate'[parameters] ),
  VAR p = 'Changes in estimate'[parameters]
  RETURN
  SWITCH(
  TRUE(),
  p = "Change in interest curve", [Change in interest curve],
  p = "Expenses", [Expenses],
  p = "Lapses", [Lapses],
  p = "Morbidity", [Morbidity],
  p = "Mortality", [Mortality],
  p = "Other estimates", [Other estimates (Changes in csm)],
  p = "Other", [CHanges in estimates Other],
  BLANK()
  )
  )
  RETURN Numerator
  
  

  Question: How can I correctly set up a Field Parameter or a Calculation Group so that when a user selects "Summary View", the Legend and the Slicer automatically collapse to those 3 categories, and when "Detailed" is selected, it expands back to 8?

  Any help or a DAX pattern for this "Dynamic Granularity" would be much appreciated!
  
  

  Please find attached two images representing the two states I want to toggle between using the switch button:

  • Image 1 (Summary View): Shows the chart with only 3 categories (Change in estimate, Other, Other estimates) as shown in the slicer and legend.

  • Image 2 (Detailed View): Shows the full breakdown of all categories (Lapses, Mortality, Expenses, etc.) as it was originally.

[tharunkumarRTK]

@AlonCarmel 

If I understand your requirement correctly, then the easiest way to achive your requirement is through a custom column or by creating a group column

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

I would suggest you to create a custom column in power query which categorizies 8 individual categories in 3

Create a field parameter table with the actual column and also the custom column. Place the field parameter column in to your visual. Then  you will be able to switch between the summary view and detailed view.

Hope this answers your question 

Connect on LinkedIn

You can read my blogs here: https://www.techietips.co.in/

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[AlonCarmel]