Forum Discussion

David23's avatar
David23
Frequent Visitor
3 years ago
Solved

Alternate matrix row colors

Hello I work with Power Bi Desktop RS. I want a dynamic background color. I explain myself with the image below: I want the lines of area 1, 3 and 5 to be with a red background color. and the li...
Need Help
David23's avatar
David23
Frequent Visitor
3 years ago

Thank you for your reply. I have 2 question plz :

 

1- how to assign CF measure to my matrix?

 

2- the list  IN { "Bi & Data", "Business Consulting", "Infrastructure" } can grow or shrink depending on what is going to be in the database. is it possible to make this line of script dynamic?

 

thanks in advance

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
3 years ago

1. The CF measure is a conditional formatting measure, so add it to the measure in the matrix.

 

2. First of all, I have set up dimension tables for the different fields.

Instead of hardcoding the values, you can include a rank measure together with the ISEVEN function  to colour the even rows in one colour and the odd rows another colour in the CF measure as follows:

CF =
VAR _Rnk =
    IF (
        ISBLANK ( [SUM Sales] ),
        BLANK (),
        CALCULATE (
            RANKX ( ALLSELECTED ( 'Pr Offer Table' ), [SUM Sales],, DESC ),
            ALLEXCEPT ( Data, 'Pr Offer Table'[Pr offer] )
        )
    )
RETURN
    IF (
        NOT ISINSCOPE ( 'Pr Offer Table'[Pr offer] ),
        "White",
        IF (
            NOT ISINSCOPE ( Data[Year - Month] ),
            "White",
            IF (
                AND ( ISINSCOPE ( 'Pr Offer Table'[Pr offer] ), ISEVEN ( _Rnk ) ),
                "#EFB5B9",
                "#A0D1FF"
            )
        )
    )

Sort the matrix by sales in descending order and you will get:

Sample PBIX file attached

  • David23's avatar
    David23
    Frequent Visitor
    3 years ago

    in my Power Bi Desktop RS, the CF variable is grayed out, I can't select it :

     

     

  • David23's avatar
    David23
    Frequent Visitor
    3 years ago

    ok. very Big thanks for you help.

     

  • David23's avatar
    David23
    Frequent Visitor
    3 years ago

    I increased the measure in the conditional formatting. but the display is not at the top :

     

     

     

    here is the script used :

     

    Me_CF2 = 
VAR _Rnk =
    IF (
        ISBLANK ( [S-Revenue_actual] ),
        BLANK (),
        CALCULATE (
            RANKX ( ALLSELECTED ( DIM_PRODUCT ), [S-Revenue_actual],, DESC ),
            ALLEXCEPT ( DIM_PRODUCT, DIM_PRODUCT[pr_offer] )
        )
    )
RETURN
    IF (
        NOT ISINSCOPE ( DIM_PRODUCT[pr_offer] ),
        "White",
        IF (
            NOT ISINSCOPE ( Dim_Time[Period] ),
            "White",
            IF (
                AND ( ISINSCOPE ( DIM_PRODUCT[pr_offer] ), ISEVEN ( _Rnk ) ), 
                "#EFB5B9",
                "#A0D1FF"
            )
        )
    )