Using Multiple Measures in a Visual on Y axis

[LyonsBI_BRL]

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]

@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

[LyonsBI_BRL · Accepted solution]

@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 🙂