Forum Discussion

kmilarov's avatar
kmilarov
Helper II
3 years ago
Solved

Table cells conditional colour formatting based on dynamic slicer selection

Guys, I have a table in my report and a slicer. The table is filtered based on the `slicer selection (VAR SelMM2 Value) between 0 and 10. Is there an option to make dynamic table cell formatting to highlight background all values B1,B2,B3,B4 if their data is less or equal to the selected slicer value SelMM2 Value ?

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey kmilarov ,

    based on your model you have to create 4 measures for the numeric values B1, B2, B3, and B4 like this one:

    vizAid B2 = 
IF(
    CALCULATE( SUM( Sheet1[B2] ) ) <= [SelMM2 Value] 
    , "orange"
    , BLANK()
)

    Then assign this measure inside the conditional formatting:


    The result will look like this (be aware that I only did this for the column B2:

    If B1, B2, B3, and B4 are categorical values then you might consider unpivoting this column using Power Query, this then will require only one measure to provid the conditional formatting at the same time.

    Hopefully, this provides what your are looking for.

    Regards,

    Tom

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    3 years ago

    Hey kmilarov ,

     

    a simple answer - yes, it's possible.

    Consider providing a pbix that contains sample data, but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, drop box, or googlddrive.
    Share the link in this thread, make sure that a login is not required to download the file.

    This article explain how to apply conditional formatting based on values (a value returned by a measure): https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting#color-by-color-values

    Hopefully, this provides some help to tackle your challenge.

     

    Regards,

    Tom

    • kmilarov's avatar
      kmilarov
      Helper II
      3 years ago

      pin2.pbix from Google drive.

       

      Thank you , Tom. I linked the file above. I have tried the instruction from your link, but failed 🙂 (ex. I cannot select the columns in the table after SWITCH('Table..) it allows me to select only the Measurements etc). Please check the file and try it 🙂 

       

      • TomMartens's avatar
        TomMartens
        Super User
        3 years ago

        Hey kmilarov ,

        based on your model you have to create 4 measures for the numeric values B1, B2, B3, and B4 like this one:

        vizAid B2 = 
IF(
    CALCULATE( SUM( Sheet1[B2] ) ) <= [SelMM2 Value] 
    , "orange"
    , BLANK()
)

        Then assign this measure inside the conditional formatting:


        The result will look like this (be aware that I only did this for the column B2:

        If B1, B2, B3, and B4 are categorical values then you might consider unpivoting this column using Power Query, this then will require only one measure to provid the conditional formatting at the same time.

        Hopefully, this provides what your are looking for.

        Regards,

        Tom