Forum Discussion
How to dynamically change columns in a Matrix or Chart
I have three components as mentioned above.
- Base data with two independent columns and one dependent column.
- A matrix to show the sum and count calculations.
- A slicer containing the column names of base data.
The question is how to dynamically change columns in matrix results when the slicer is changed from Column1 to Column2?
Note: I can't pivot the table as I have other calculations also running on top of the base data. And I have 70+ columns and 5M rows of data. So pivoting or creating a union-like table would make a 350M+ rows table which I don't need.
Thank you.
I think new feture field parameters from Power BI May update 2022 will help you
https://www.youtube.com/watch?v=LTdpe2ENW4M&t=177s
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
11 Replies
- PaulDBrown
Community Champion
Actually there is a way to accomplish this without the new fields parameter option. Bascially you need to build a table to use as the slicer listing the values of all the columns you need in a single column. Take this data as an example:
To be able to filter the item, colour and type columns, we need to create a table to use as a slicer listing all the values & the column name (& and order column for sorting purposes.
To create the new table, you can append the columns in Power Query or use the following DAX:
Select Column Table = VAR _item = SELECTCOLUMNS ( FactTable, "SelColumn", FactTable[Item], "ColumnName", "Item", "Order", 1 ) VAR _colour = SELECTCOLUMNS ( FactTable, "SelColumn", FactTable[Colour], "ColumnName", "Colour", "Order", 2 ) VAR _type = SELECTCOLUMNS ( FactTable, "SelColumn", FactTable[Type], "ColumnName", "Type", "Order", 3 ) RETURN DISTINCT ( UNION ( _item, _colour, _type ) )to get..
Next create relationships between the SelColumn field in this new table and each of the columns in the fact table:
Then a simple SUM measure and the following measure to use in the matrix:
Select Column Measure = SWITCH ( SELECTEDVALUE ( 'Select Column Table'[ColumnName] ), "Item", [Sum Value], "Colour", CALCULATE ( [Sum Value], USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Colour] ) ), "Type", CALCULATE ( [Sum Value], USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Type] ) ) )or if you also need the grand total (which I don´t think makes much sense since the result is multiplied by the number of columns selected):
With totals = SUMX ( 'Select Column Table', CALCULATE ( SWITCH ( SELECTEDVALUE ( 'Select Column Table'[ColumnName] ), "Item", [Sum Value], "Colour", CALCULATE ( [Sum Value], USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Colour] ) ), "Type", CALCULATE ( [Sum Value], USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Type] ) ) ) ) )To get:
I've attached the sample PBIX file
- jothi_prakash_aFrequent Visitor
This is a viable solution. In my case, the data would be too long. 428M rows of data to be precise. That increases the size of the PBIX file to nearly 1.5GB which causes trouble while hosting. As there's a 1Gb upload limit.
- SolomonovAnton
Super User
I think new feture field parameters from Power BI May update 2022 will help you
https://www.youtube.com/watch?v=LTdpe2ENW4M&t=177s
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
- jothi_prakash_aFrequent Visitor
This is a great solution for the May 2022 version, SolomonovAnton.
I'm using May 2021 because the Report server is compatible with it. So if there's a solution to May 2021 Please share, I'll accept this as the solution too.
- PaulDBrown
Community Champion
Create a new unrelated table (use the Enter Data option in the ribbon an type in the names of the measures you wish to toggle between). Use this table as the slicer.
next create the following equivalent measure to use in the matrix:
toggle = IF(SELECTEDVALUE(SlicerTable[Measure]) = "Measure 1", [Measure 1], [Measure 2])
- mahenkj2
Solution Sage
Hi jothi_prakash_a ,
If this report is published, you can give users the right to personalize as needed.
In that case, they can select whatever column they need and it will be right away done.
Hope it helps.