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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LyonsBI_BRL
Helper III
Helper III

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. 

LyonsBI_BRL_0-1651044455489.png

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

 

LyonsBI_BRL_1-1651045232532.png

In the end what I want is the ability to create a bar chart similar to the one below

LyonsBI_BRL_2-1651045317462.png

 

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())

LyonsBI_BRL_3-1651045355463.png

 

Any suggestions would be greatly appreciated. 

 

Thanks!

 

 

 

 

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

LyonsBI_BRL_0-1651129238758.png

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

LyonsBI_BRL_0-1651129238758.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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