Forum Discussion
How to dynamically change columns in a Matrix or Chart
- 4 years ago
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
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_a4 years agoFrequent 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.