Forum Discussion
Show multiple measure based on single slicer selection
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.
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])
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:
Best Regards,
Giotto Zhi
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
10 Replies
- v-gizhi-msft
Community Support
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:
Best Regards,
Giotto Zhi
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
pbiforum123Post Patron
v-gizhi-msft Sample file which you have uploaded is not available now. Could you please reshare the file.
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
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?
