Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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.
Please see the attached PBIX .
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
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.
Please see the attached PBIX .
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!!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |