Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi Power BI Community!
I am in need of some help optimizing the efficiency of a slicer forged from a field parameter to control the metrics within my comparative area chart which uses small multiples. Here is an example of the issue I am having:
CONTEXT TO START:
Suppose this is my dataset: Which I have made these 6 measures for:
DETAILS:
The measures were constructed use this concept of DAX:
This is the field parameter built off those 6 measures:
CURRENT STATE:
I have an area chart with small multiples by school that compares my institution (Lakeview) to the other institutions (Bradford, Rockvill, etc), controlled by a Slicer forged from a field parameter that traces to the 6 separate measures.
PROBLEM:
What I want is for users to have a slicer that looks like (below). When a user selects enrollment the area chart shows exactly what you see above (both Lakeview and the competitor school. I don't want them to have to correctly select both "enrollment" options. Essentially, the area chart should always have two lines and be comparing, instead of my current state above where the user has to correctly select both options for Lakeview and Total.
ATTEMPTED OR FORESEEABLE SOLUTIONS:
1. I tried reprogramming the field parameter so it considers two separate measures as one option (but the NAMEOF functions doesn't like this)
2. IF there was a way to make a measure output two quantitative values (so instead of LakeviewEnrollment and TotalEnrollment just have an enrollment measure that somehow outputs Lakeview's Enrollment AND the Total Enrollment of the other school that is selected) then I think this could work because the field parameter would only have to be built off of 3 measures instead of 6. I'm not sure if that is even possible though...
I hope this explanation is clear and that someone can help me, because I think field parameters are great, but if I can't find a way to utilize them anytime I am comparing two organizations...what's the point then?
Solved! Go to Solution.
Thanks @johnt75 for the proposed solution.
I ended up copying the dataset and pivoting the Enrollment, Staff, and Diplomas columns. This allowed me to make a slicer based off a new column called "Metric" instead of having a slicer based off a field parameter. From there, I could now make a new measure for Lakeview based off the selected value the user had chosen in the slicer by utlizing the SWITCH function in DAX.
@paige_kinney Did this answer your question? If so please click [accept solution] and thank the helper or give feedback. Thank you.
I think you could do this with a combination of a field parameter and a calculation group.
Create 3 measures, [Enrollment], [Staff] and [Diplomas] as simple SUM of the appropriate column. Create a field parameter with these measures as the values.
Then create a calculation group with 2 calculation items.
Lakeside calc item =
CALCULATE ( SELECTEDMEASURE (), 'Highschool Facts'[School] = "Lakeview High" )
Total calc item =
CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Highschool Facts'[School] ) )
Thanks @johnt75 for the proposed solution.
I ended up copying the dataset and pivoting the Enrollment, Staff, and Diplomas columns. This allowed me to make a slicer based off a new column called "Metric" instead of having a slicer based off a field parameter. From there, I could now make a new measure for Lakeview based off the selected value the user had chosen in the slicer by utlizing the SWITCH function in DAX.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
129 | |
78 | |
76 | |
60 | |
53 |
User | Count |
---|---|
164 | |
86 | |
68 | |
68 | |
58 |