Power BI - Dynamic View Options - Matrix

Question

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!

vicky_ · Answer

Assuming your columns are DAX measures, you can use field parameters for that:&nbsp;https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

hallmarke14 · Answer

I will give that a try!&nbsp; Thank you, Vicky!

anonymous · Answer

Hi&nbsp;hallmarke14&nbsp;,
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] &lt; 0, "Red", "Green")
%CHGSalesColor = IF([%CHGSalesShow] &lt; 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%")
)

&nbsp;
&nbsp;
&nbsp;
Best Regards
Yilong Zhou
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

hallmarke14 · Answer

Hi, Vicky.&nbsp; Just wanted to thank you for the tip and provide an update.&nbsp; We use Analysis Services, so we have a Live Connection for this particular report.&nbsp; 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:&nbsp;SelectedMeasureShow =SWITCH(&nbsp; &nbsp; SELECTEDVALUE(ColumnSelector[Column]),&nbsp; &nbsp; "Units TY", FORMAT([UnitsTYShow], "#,##0"),&nbsp; &nbsp; "Units SDLY", FORMAT([UnitsSDLYShow], "#,##0"),&nbsp; &nbsp; "% CHG Units", FORMAT([%CHGUnitsShow], "0.0%"),&nbsp; &nbsp; "Sales TY", FORMAT([SalesTYShow], "$#,##0"),&nbsp; &nbsp; "Sales SDLY", FORMAT([SalesSDLYShow], "$#,##0"),&nbsp; &nbsp; "% CHG Sales", FORMAT([%CHGSalesShow], "0.0%"))&nbsp;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.&nbsp; 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!

hallmarke14 · Answer

Thanks, Yilong!&nbsp;I tried adding the color measures, but they don't actually change the color of the values.&nbsp; All it did was show the string values "red" or "green" based on the IF condition.&nbsp; I was hopeful when I saw your idea.&nbsp; I appreciate your effort!