Forum Discussion

LamSar's avatar
LamSar
Helper III
2 years ago

Apply different Background Color for each item in calculation group

Good afternoon,

I created a Calculation group with 9 items. 

I now need to add specific background colors to specific items in the caluculation group.

The first two columns should have background color yellow

Column 3 and 4: Green

Column 5 and 6; Blue

Column 7 and 8: Pink

And colum 9: No background color. 

 

If tried it using multiple measures, but it doesn't work.

This is one thing I tried:

VAR HuidigeSelectie =
                SELECTEDVALUE ( 'Promodruk'[PromotieCategorie] )
            RETURN
                IF (
                    HuidigeSelectie IN { "Vaste Promo", "Aandeel Vaste Promo" },
                    "#33908A",
                    IF (
                        HuidigeSelectie IN { "FolderPromo", "Aandeel FolderPromo" },
                        "#EF7174",
                        IF (
                            HuidigeSelectie IN { "Andere Promo", "Aandeel Andere Promo" },
                            "#D0C126",
                            IF (
                                HuidigeSelectie IN { "Niet-Promo", "andeel Niet-Promo" },
                                "#6695C5",
                                BLANK ()
                            )
                        )
                    )
                )
 

 How could I achieve this?

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello LamSar,

     

    Can you please try this improved DAX:

    Background Color Measure = 
VAR CurrentSelection = SELECTEDMEASURENAME()
RETURN
    SWITCH(
        TRUE(),
        CurrentSelection = "YourCalcGroupItem1" || CurrentSelection = "YourCalcGroupItem2", "#FFFF00",  // Yellow
        CurrentSelection = "YourCalcGroupItem3" || CurrentSelection = "YourCalcGroupItem4", "#008000",  // Green
        CurrentSelection = "YourCalcGroupItem5" || CurrentSelection = "YourCalcGroupItem6", "#0000FF",  // Blue
        CurrentSelection = "YourCalcGroupItem7" || CurrentSelection = "YourCalcGroupItem8", "#FFC0CB",  // Pink
        BLANK()  // No color for Column 9 and others
    )
    • LamSar's avatar
      LamSar
      Helper III
      2 years ago

      Good morning Sahir,

      This doesn't work. 

      When I use this measure nothing happens. 

       

      I added the measure to the matrix to see if it returns the color codes, but it returns the same value of the calculation items itself:

       

    • LamSar's avatar
      LamSar
      Helper III
      2 years ago

      I also tried the manual of SQLBI: https://www.sqlbi.com/articles/using-field-parameters-and-calculation-groups-for-conditional-formatting/

       

      I created a calculated table with the column names of the calculation items. Calculated TableName Promodruk CG Omschrijving. 

      Then adjusted the calculation items as follows:

      Example of one the items - FolderPromo:

      VAR berekening =
    CALCULATE (
        SELECTEDMEASURE (),
        FILTER (
            Promo,
            Promo[IsVastePromo] = 0
                && Promo[PromoCategorie] = "Standaardpromotie"
        ),
        'Promodruk CG Omschrijving'[PromotieCategorie] = "FolderPromo"
    )
RETURN
    IF ( ISSELECTEDMEASURE ( [Dynamic Color CG Promodruk] ), Berekening )

      And then I created this measure for the background:

      Dynamic Color CG Promodruk:=
VAR Huidigveld =
    SELECTEDVALUE ( 'Promodruk CG Omschrijving'[PromotieCategorie] )
VAR Color =
    SWITCH (
        Huidigveld,
        "Vaste Promo", "Groen",
        "Aandeel Vaste Promo", "Groen",
        "FolderPromo", "Rood",
        "Aandeel FolderPromo", "Rood",
        "Andere Promo", "Geel",
        "Aandeel Andere Promo", "Geel",
        "Niet-Promo", "Blauw",
        "Andeel Niet-Promo", "Blauw",
        BLANK ()
    )
RETURN
    Color

       But it does not return the desired result: I don't get any data back from the calculation group (the matrix is empty) and the color also does not work.