Having a dynamic measure depending on different selections

[Anonymous]

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

[amitchandak · Accepted solution]

@Anonymous ,Based on what I got. with help from independent table you can try measure slicer

measure slicer
my video: https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

you can also explore calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

[Anonymous]

Thanks for the help, both of you! 🙂


Sincerely
Ash

[Adescrit · Accepted solution]

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.

[amitchandak · Accepted solution]

@Anonymous ,Based on what I got. with help from independent table you can try measure slicer

measure slicer
my video: https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

you can also explore calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/