Forum Discussion
Power BI - Dynamic View Options - Matrix
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
I will give that a try! Thank you, Vicky!
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:
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([%CHGSalesShow], "0.0%"))I created a slicer that uses the column table and it works very nicely, but since the matrix uses one measure, I am not able to implement conditional formating for the % measures (red for negative numbers, and green for postive) without it affecting all of the results. I would like to be able to have the matrix to accept multiple selections, but it doesn't like it when I select more than one measure.
If anyone has any ideas or a tricks to allowing conditional formatting when there is only one measure or a way to be able to select multiple selections to expand the matrix beyond the initial 6 columns, that would be greatly appreciated!
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!
