Show multiple measure based on single slicer selection

[bibhu250]

Hi All,

My requirement is as follows. I have created 6 measures that belong to 3 different categories(2 meaures for each category). For example, my categories are

OOG(measures are OOG CY, OOG PY),

 IIF(IIF CY, IIF PY) 

 ZF(ZF CY, ZF PY).

I want to show "upon single selection of a slicer", its dependent measures. So, when I select OOG from slicer, I want to see measures CY & PY in a line chart. Right now, I am unable to achieve this functionality, so I am showing every measure as a slicer value via implementation of a helper table and below DAX.

IIF CY % = IF(SELECTEDVALUE(SlicerTable[Slicer Value])=3||ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])),[IIF CY])

Instead, when I select "OOG", I want to see both OOG & OOG PY in the line chart. Something like this below.

I also tried this DAX below, but it didnt work. Can any one suggest some ideas here ?

MeasureSelection =
VAR MySelection =
SELECTEDVALUE('SlicerHelper'[Category],"OOG")
RETURN
SWITCH(
TRUE(),
MySelection="OOG",[OOG CY],
MySelection="OOG",[OOG PY],
MySelection="IIF",[IIF CY],
MySelection="IIF",[IIF PY],
MySelection="Zero Fill",[ZR CY],
MySelection="Zero Fill",[ZR PY],
[OOG CY])

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, I create a table to test:

Please take following steps:

1)Create six measures like you:

OOG CY = SUM(Sales[Sales])

OOG PY = SUM(Sales[Sales])*10

IIF CY = SUM(Sales[Sales])*20

IIF PY = SUM(Sales[Sales])*30

ZF CY  = SUM(Sales[Sales])*40

ZF PY  = SUM(Sales[Sales])*50

2)Create a table by ‘Enter Data’:

3)Create a measure to display what you want:

Measure

= SWITCH(SELECTEDVALUE(SlicerTable[SlicerValue]),"OOG CY",[OOG CY],"OOG PY",[OOG PY],"IIF CY",[IIF CY],"IIF PY",[IIF PY],"ZF CY",[ZF CY],"ZF PY",[ZF PY])

4)Choose [BelongTo] as a slicer and the result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[bibhu250 · Accepted solution]

Bravo ! My issue is fixed. Thanks much !

[v-gizhi-msft · Accepted solution]

Hi,

Please add a new column [Index] in Query Editor:

Then, change this table sort by [Index] in Modeling pane:

And the visual shows the PY first and CY second:

Best Regards,

Giotto Zhi

[Anonymous]

Hi!

I face the same problem as @mmendizIgt, so the switch statement is not working. How is it possible to return multiple measures, can you share an example?

[mmendizIgt]

Hello,

Did one of you still have the example pbix? For some reason, the switch measure doesn't work for me, when I want to show multiple values with one selection as in the example OOG selected and showing OOG PY and OOG CY. 

Best regards,

Mijalis

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, I create a table to test:

Please take following steps:

1)Create six measures like you:

OOG CY = SUM(Sales[Sales])

OOG PY = SUM(Sales[Sales])*10

IIF CY = SUM(Sales[Sales])*20

IIF PY = SUM(Sales[Sales])*30

ZF CY  = SUM(Sales[Sales])*40

ZF PY  = SUM(Sales[Sales])*50

2)Create a table by ‘Enter Data’:

3)Create a measure to display what you want:

Measure

= SWITCH(SELECTEDVALUE(SlicerTable[SlicerValue]),"OOG CY",[OOG CY],"OOG PY",[OOG PY],"IIF CY",[IIF CY],"IIF PY",[IIF PY],"ZF CY",[ZF CY],"ZF PY",[ZF PY])

4)Choose [BelongTo] as a slicer and the result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[pbiforum123]

@v-gizhi-msft Sample file which you have uploaded is not available now. Could you please reshare the file.

[bibhu250]

Thanks,

Your solution did fix my issue, but my numbers are being rounded for some reason. I even modified the SWITCH statement by adding a ROUND function outside the measure, but no luck.

Measure

= SWITCH(SELECTEDVALUE(SlicerTable[SlicerValue]),

"OOG CY", ROUND([OOG CY],2),

"OOG PY",ROUND([OOG PY],2),

"IIF CY",ROUND([IIF CY],2),

"IIF PY",ROUND([IIF PY],2),

"ZF CY",ROUND([ZF CY],2),

"ZF PY",ROUND([ZF PY],2))

Also, I tried to modify the calculation of measure by including ROUND at source, still it didn't help.

OOG CY= ROUND((divide([OOGCounts],[Total_Counts]))*100,2).

OOGCounts = CALCULATE(COUNT(table), table.column = "OOG")

Any ideas where I am going wrong??

[v-gizhi-msft]

Hi,

According to your description, I think your original data is decimal format and is has been rounded after using the measure I gave, right?

If so, please check the original data format:

If you have not set it, the data will be automatically rounded to the whole number format.

Best Regards,

Giotto Zhi

[bibhu250 · Accepted solution]

Bravo ! My issue is fixed. Thanks much !

[bibhu250]

@v-gizhi-msft 

Hi, in your previous solution, I just changed the line chart into a clustered column chart. Hence, now I have 2 vertical bars(belonging to 2 selected measures) for each date. Please see screenshot below.

Is there any way I can order the legend as OOG PY first, then OOG CY. In other words, for all selected values, it should show the  PY value first followed by the CY value?

I tried to duplcate the same table and entered the data in the required order, even added a numeric column to the table and sorted the records, but no luck. I also recreated the measure in order of the display I want, but that didn't help. 

[v-gizhi-msft · Accepted solution]

Hi,

Please add a new column [Index] in Query Editor:

Then, change this table sort by [Index] in Modeling pane:

And the visual shows the PY first and CY second:

Best Regards,

Giotto Zhi

[bibhu250 · Accepted solution]

It did work. Thanks !