cancel
Showing results 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.

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.

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!

1 ACCEPTED SOLUTION
Helper III

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

Helper III

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors