Forum Discussion
Using Multiple Measures in a Visual on Y axis
I've got kind of a challenging problem here on creating two visuals. One is for age and one is for subject. Where within the data model I have two columns where they are coma delimited.
In order to prevent having 900,000 rows per person, I created several columns using M query looking to see if they contain what age range or what subject they are teaching for example below
each if Text.Contains([AgeRange], "6 – 8 years old") then true else false
In the end what I want is the ability to create a bar chart similar to the one below
And less like this, which is what I'm getting now from multiple measures. Where I only have the measures inside the Values part of the bar chart, though this looks like a mess. Each measure is formatted as such
Age 12 - 14 yr olds = CALCULATE(
COUNTROWS('All Applicants - 2021 (2)'),'All Applicants - 2021 (2)'[12 - 14 years old] = TRUE())
Any suggestions would be greatly appreciated.
Thanks!
amitchandak Thanks for the suggestion. However after looking more into this, I figured out what I was missing here in order to get this to work
Below is what I did.
Step 1 - Create Slicer Table
Age Slicer = { "0 – 5 years old", "6 – 8 years old", "9 – 11 years old", "12 – 14 years old", "15 – 18 years old", "Students over 18 years old" }Step 2 Create a Meaurse that I can use to move back and forth
Age Selection = SELECTEDVALUE('Age Slicer'[Value])Step 3 - Create Measure that uses the SWITCH function in order to sort each measure.
Age Selected Values = SWITCH( TRUE(), [Age Selection] = "0 – 5 years old", [Age 0 – 5 years old], [Age Selection] = "6 – 8 years old", [Age 6 - 8 yrs old], [Age Selection] = "9 – 11 years old", [Age 9 – 11 yrs old], [Age Selection] = "12 – 14 years old", [Age 12 - 14 yr olds], [Age Selection] = "15 – 18 years old", [Age 15 - 18 yrs old], [Age Selection] = "Students over 18 years old", [Age over 18], "Other Age Groups" )Which gives me the result I'm looking for 🙂
2 Replies
LyonsBI_BRL , Not very clear, but create a calculation group to use them again as rows
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
amitchandak Thanks for the suggestion. However after looking more into this, I figured out what I was missing here in order to get this to work
Below is what I did.
Step 1 - Create Slicer Table
Age Slicer = { "0 – 5 years old", "6 – 8 years old", "9 – 11 years old", "12 – 14 years old", "15 – 18 years old", "Students over 18 years old" }Step 2 Create a Meaurse that I can use to move back and forth
Age Selection = SELECTEDVALUE('Age Slicer'[Value])Step 3 - Create Measure that uses the SWITCH function in order to sort each measure.
Age Selected Values = SWITCH( TRUE(), [Age Selection] = "0 – 5 years old", [Age 0 – 5 years old], [Age Selection] = "6 – 8 years old", [Age 6 - 8 yrs old], [Age Selection] = "9 – 11 years old", [Age 9 – 11 yrs old], [Age Selection] = "12 – 14 years old", [Age 12 - 14 yr olds], [Age Selection] = "15 – 18 years old", [Age 15 - 18 yrs old], [Age Selection] = "Students over 18 years old", [Age over 18], "Other Age Groups" )Which gives me the result I'm looking for 🙂
