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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Having a dynamic measure depending on different selections

Hi,

I’m trying to create a conditional measure depending on slicer choices. I have three categories: Culture, Studies and Other.

 

For Studies and Other, I’m counting study hours. But for Culture, I’m counting number of occasions instead.

I have the expressions for both of these measures that look something like this:

Study hours = CALCULATE(
SUM(hours)
,view_Event[Type] IN {1, 3} --“Studies” and “Other”
)

 

Culture Occasions = CALCULATE(
COUNT(Occasions)
,view_Event[Type] IN {2} --"Culture”
)


I would like to have a single bar chart with a slicer next to it so that users can choose if they want to see Study Hours or Culture occasions. If possible, I would like to have an outcome so that I can use the new conditional measure in cards and gauges as well.

 

So, the users should be able to choose either Studies and Other (or both) and it would show the number of hours. Or choose Culture and it should show number of occasions instead. They should not be able to mix these two different types.


Sincerely
Ash

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

Adescrit
Impactful Individual
Impactful Individual

Create a new table using Enter Data in the data view of Power BI desktop. In this table create two columns "Metric" containing your desired slicer options (Culture, Studies, Other) and an "Index" column containing the numbers 1 to 3. Using the Enter Data option means you can just type all this in. Name the table "Metric Selection".

In this new table create a measure:

 

 

Selected Metric = SELECTEDVALUE( 'Metric Selection'[Metric] )

 

 

 

Now you can create a measure that allows the user to toggle between the measures you have already created, depending on what they select from a slicer:

 

 

 

Culture, Studies Other Selection = 
    SWITCH( TRUE() ,
        'Metric Selection'[Selected Metric] = "Culture", [Culture Occasions],
        'Metric Selection'[Selected Metric] = "Studies", [Study hours]
    )

 

 

 

You can use this measure in the values section of your charts, graphs and cards.


Did I answer your question? Mark my post as a solution!
My LinkedIn

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Thanks for the help, both of you! 🙂


Sincerely
Ash

Adescrit
Impactful Individual
Impactful Individual

Create a new table using Enter Data in the data view of Power BI desktop. In this table create two columns "Metric" containing your desired slicer options (Culture, Studies, Other) and an "Index" column containing the numbers 1 to 3. Using the Enter Data option means you can just type all this in. Name the table "Metric Selection".

In this new table create a measure:

 

 

Selected Metric = SELECTEDVALUE( 'Metric Selection'[Metric] )

 

 

 

Now you can create a measure that allows the user to toggle between the measures you have already created, depending on what they select from a slicer:

 

 

 

Culture, Studies Other Selection = 
    SWITCH( TRUE() ,
        'Metric Selection'[Selected Metric] = "Culture", [Culture Occasions],
        'Metric Selection'[Selected Metric] = "Studies", [Study hours]
    )

 

 

 

You can use this measure in the values section of your charts, graphs and cards.


Did I answer your question? Mark my post as a solution!
My LinkedIn
amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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