topic Re: Matrix with multiple slicers in DAX Commands and Tips

Matrix with multiple slicers

Anonymous — Fri, 25 Mar 2022 17:18:54 GMT

Hello,

I have a source with several millions data and columns count also high too. Without using Unpivot (data volume is high) how can I achieve this.

Source with dimensions and measures . I have to display list of dimensions in one slicer and measures in another slicer. As per the selection in both slicers I have to display selected columns in the Matirx. Additional point is user can select multiple selections from the each slicer too .Very intercative dashboard requirement it is.Based on Key column I need to display this data in Matrix.

I have created two tables one for selected Dimensions and one for Measures.Created calculated measures with Switch and selectedvalue functions. And I tried with different matrix for different slicers. It is working fine.

But both slicers selection result in single Matrix. Is it possible?

Re: Matrix with multiple slicers

amitchandak — Wed, 23 Mar 2022 03:53:50 GMT

Anonymous , replacing and adding of measure is easy, you can use calculation group. of even you can use measure slicer without using calculation group.

If you do not want unpivot then personalization option can help. All others need some some transformation

measure slicer
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
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME

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

For axis/row/column refer

Axis Slicer
Three approaches from Guyinacube - https://www.youtube.com/watch?v=dYmzb5UMkXw
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Re: Matrix with multiple slicers

Anonymous — Wed, 23 Mar 2022 14:28:35 GMT

Thank you for replying Amit.

I think I am missing something. With calculated measure I am able to add/remove selected measures in Matrix as expected. Also in the same way I am able to display dimensions in another Matrix. But issue is both selectied columns from Measures and Dimensions of two slicers,I have to display in the same Matrix. Is there any Dax function I can use to switch between mutiple slicers selection? Thanks in advance.

Re: Matrix with multiple slicers

Anonymous — Fri, 25 Mar 2022 09:49:30 GMT

Hi Anonymous ,

Create a new table with a column contains "mesaure1", “measure2”, “measure3, ...ect.

Use this column as slicer.

Then create a new measure like:

Measure =

SWITCH(selectedvalue('slicer'[measurename]),

"measure1",[measure1],

"measure2",[measure2],

"measure3',[measure3])

If I misunderstand your meaning, please show some sample data and expected result.

Best Regards,

Jay