Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
10500438
Helper II
Helper II

Switching Dimensions Based on Slicer Selection

Disclaimer: ​I am certainly no expert, but not a newbie.

I found a solution to change measures ($$, $/ton, etc) using the SWITCH DAX but I need to change the DETAILS and LEGEND in the Scattergraph. (for my chart these two would always be the same, although I know they can be different)

We describe our data in two ways, by Product Group and by Type. I want to create a single scattergraph that can toggle between Product Group and Type by selecting some sort of slicer or selection tick box.

For example, my scattergraph has Cost on X Axis and Revenue on Y Axis and Product Group is my Details and Legend. if the user would rather see it by Type, they click type and it would change the details to Type.

I know this is possible using switch to change the Cost to another measure type such as Discounts, etc. But can it be done with dimensions?

Thanks for any advice you can offer.

PS we really describe our data in about 10 different ways, so building charts and graphs for all these views and then different views based on contribution margin, ebitda margin, etc makes for a huge report.

1 ACCEPTED SOLUTION
itsmebvk
Continued Contributor
Continued Contributor

@10500438

 

Please see the attached PBIX .

 

Follow these steps:

 

1) Import data in to Power BI I am using excel as source

2) Then you need to insert index column on Query 1 (if you don't have any key column)

3) Then you need to duplicate your Query1 as Query2

4) Then remove unnecessary columns from Query2 except the columns you want in slicer and Index column. In this example I have used Plant,Turbine, Index

5) Then select Plant and Turbine column in Query 2>Right Click>Unpivot

6) Now you will see new column names as Attribute and Values in Query2

7) Now use Query 2 Attribute column as slicer

😎 Add a bar chart to the report then add Value from Query2 to Bar chart

9) Add other measures or dimensions from Query1 to chart

10) Now you should be able to switch dimensions using slicer, but if you observe data is showing incorrectly on charts, this is because of Cross Filter Direction set to SIngle

 

Wrong Chart.PNG

 No Cross Filter'.PNG

 

 

 

11) You can fix this issue by changing cross filter direction to both or creating new measure using following code

 

Calculated Alarrm Count = CALCULATE(SUM('Query1'[Alarm_Count]),CROSSFILTER(Query2[Index],'Query1'[Index],Both))


I strongly suggest you to do it using Calculated Measure or instead of changing Join

 

12) Now add newly created measure to the report , it should display data as expected.

 

Working.PNG 

 

 Please see the attached PBIX .

View solution in original post

3 REPLIES 3
itsmebvk
Continued Contributor
Continued Contributor

@10500438

 

Please see the attached PBIX .

 

Follow these steps:

 

1) Import data in to Power BI I am using excel as source

2) Then you need to insert index column on Query 1 (if you don't have any key column)

3) Then you need to duplicate your Query1 as Query2

4) Then remove unnecessary columns from Query2 except the columns you want in slicer and Index column. In this example I have used Plant,Turbine, Index

5) Then select Plant and Turbine column in Query 2>Right Click>Unpivot

6) Now you will see new column names as Attribute and Values in Query2

7) Now use Query 2 Attribute column as slicer

😎 Add a bar chart to the report then add Value from Query2 to Bar chart

9) Add other measures or dimensions from Query1 to chart

10) Now you should be able to switch dimensions using slicer, but if you observe data is showing incorrectly on charts, this is because of Cross Filter Direction set to SIngle

 

Wrong Chart.PNG

 No Cross Filter'.PNG

 

 

 

11) You can fix this issue by changing cross filter direction to both or creating new measure using following code

 

Calculated Alarrm Count = CALCULATE(SUM('Query1'[Alarm_Count]),CROSSFILTER(Query2[Index],'Query1'[Index],Both))


I strongly suggest you to do it using Calculated Measure or instead of changing Join

 

12) Now add newly created measure to the report , it should display data as expected.

 

Working.PNG 

 

 Please see the attached PBIX .

@itsmebvk

Thank You! This definetely would work as a solution and I will use it for other things.

 

After posting, I found something called BookMarks, it allowed me to do the same thing relatively easy.

I created two charts and layered them, using the bookmarks function, I swapped the visuals easily.

 

THANKS AGAIN!!

itsmebvk
Continued Contributor
Continued Contributor

@10500438

 

Glad it we worked for you. Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.