Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I have been asked to make a matrix dynamic in a sense that you could check and uncheck a box to only have certain columns show or hide. Attached is a mockup:
Any ideas or suggestions will be greatly appreciated!
Assuming your columns are DAX measures, you can use field parameters for that: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
Hi, Vicky. Just wanted to thank you for the tip and provide an update. We use Analysis Services, so we have a Live Connection for this particular report. I understand that there is a limitation on that, so I found an alternative solution, which was to create a table in SSAS of the names of the columns and create the following measure:
Hi @hallmarke14 ,
I create a table as you mentioned.
Then I think you can create some measures and here are the DAX codes.
UnitsTYShow = SUM(SalesData[UnitsTY])
UnitsSDLYShow = SUM(SalesData[UnitsSDLY])
%CHGUnitsShow = DIVIDE([UnitsTYShow] - [UnitsSDLYShow], [UnitsSDLYShow], 0)
SalesTYShow = SUM(SalesData[SalesTY])
SalesSDLYShow = SUM(SalesData[SalesSDLY])
%CHGSalesShow = DIVIDE([SalesTYShow] - [SalesSDLYShow], [SalesSDLYShow], 0)
You can also create colors on them.
%CHGUnitsColor = IF([%CHGUnitsShow] < 0, "Red", "Green")
%CHGSalesColor = IF([%CHGSalesShow] < 0, "Red", "Green")
Then I create a new table and also create a measure.
ColumnSelector =
DATATABLE(
"Column", STRING,
{
{"Units TY"},
{"Units SDLY"},
{"% CHG Units"},
{"Sales TY"},
{"Sales SDLY"},
{"% CHG Sales"}
}
)
SelectedMeasureShow =
SWITCH(
SELECTEDVALUE(ColumnSelector[Column]),
"Units TY", FORMAT([UnitsTYShow], "#,##0"),
"Units SDLY", FORMAT([UnitsSDLYShow], "#,##0"),
"% CHG Units", FORMAT([%CHGUnitsShow], "0.0%"),
"Sales TY", FORMAT([SalesTYShow], "$#,##0"),
"Sales SDLY", FORMAT([SalesSDLYShow], "$#,##0"),
"% CHG Sales", FORMAT([%CHGUnitsShow], "0.0%")
)
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, Yilong!
I tried adding the color measures, but they don't actually change the color of the values. All it did was show the string values "red" or "green" based on the IF condition. I was hopeful when I saw your idea. I appreciate your effort!
Hi @hallmarke14 ,
If you are only coloring other columns, in Powerbi you can just select Conditional formatting to change it.
But if you want to colorize based on your choices in Slicer, my suggestion would be to aggregate Measure in the same format for modification.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have only one measure in Values and that populates the matrix. We have several Channels, which is what our columns are that we want for comparison. If I choose conditional formatting for that measure, the changes apply to all values, not just the % Chg columns.
If I apply conditional formatting, these values will be colorized, but when I change the selection to Units, those are colorized, as well, and we do not want that.
I will give that a try! Thank you, Vicky!
User | Count |
---|---|
94 | |
92 | |
84 | |
83 | |
49 |
User | Count |
---|---|
150 | |
146 | |
112 | |
73 | |
56 |