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 ...
Need Help
Show and Tell
Tips and Tricks
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

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.