Power BI - Dynamic View Options - Matrix

[hallmarke14]

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_]

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

[hallmarke14]

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!

[Anonymous]

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.

[hallmarke14]

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!

[Anonymous]

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.

[hallmarke14]

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.

[hallmarke14]

I will give that a try!  Thank you, Vicky!