Conditional formatting in Power BI with calculation groups (for multiple measures at once)

[andrejmfreitas]

Hello all,

Imagine the following situation: 

I've created calculate items for multiple KPIs (TY, vs LY (%), vs Target (%)..etc). That works for all measures (Total orders, Total revenue..etc)

I put them all on the table. They work fine.

But now, I want only to format vs LY and vs Target for all the measures.

There are many tutorials online that show how to Conditional formatting in Power BI with calculation groups but only for one measure at a time, not for them all..which it's pretty time-consuming. For instance, this guy explains this well: https://www.youtube.com/watch?v=ToAlPA5KvUc

The way he explained, is like this:

1. Create a DAX measure that selects only vs LY and vs Target for each of the measures.

2. Put them in the conditional format.

Something like this: 

Color_Net revenue =
IF(
SELECTEDVALUE('TI Funnel Checkout'[FC]) IN {"vs LY, vs Target"},
[Net revenue]
)

Now if I want to create the same for Net orders, I'd have to create another DAX measure like this:

Color_Net order =
IF(
SELECTEDVALUE('TI Funnel Checkout'[FC]) IN {"vs LY, vs Target"},
[Net order]
)

..

so on...

This is not efficient and loses the whole idea of calculation groups - since I need to create one formatting measure for each of my measures.

I'd like to create only a single measure, but I have not found a way to do this yet. Can you guys help? Maybe we can come up with a nice solution together.

I was thinking something like this:

Color =
IF(
SELECTEDVALUE('TI Funnel Checkout'[FC]) IN {""vs LY, vs Target""},
SWITCH(
TRUE(),
ISSELECTEDMEASURE([Net revenue]), [Net revenue],
ISSELECTEDMEASURE([Net units]), [Net units]

(..etc)
)
)

In that way, I can put only a single measure in all my calculated items. But that does not work.

Any ideas?

[andrejmfreitas]

Hey, thanks, but I tried that and it does not work because SELECTEDMEASURENAME () does not work in a Measure. 
We should find a way to make SELECTEDMEASURENAME () in a measure. (we do need it as a measure, not a calculated item to make the conditional formatting) 

[v-shex-msft]

Hi @andrejmfreitas,

Have you tried to use SELECTEDMEASURE function? As the document said, it will used by expressions for calculation items to reference the measure that is in context.

SELECTEDMEASURE function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng

[andrejmfreitas]

Yes, I've tried.

PBI accepts the measure as "Correct". However, the conditional formatting does work. Therefore, none of these SELECTEDMEASURENAME or SELECTEDMEASURE works in a measure for Conditional formarting unfortuntely.

ChatGPT gave me some tips, but none of them worked either.

This would be a life-changer for advanced DAX development in my view.

[v-shex-msft]

HI @andrejmfreitas,

Perhaps you can try to use switch wiht SELECTEDMEASURENAME or directly use SELECTEDMEASURE function to return the current evaluate measure result:

formula =
VAR currFC =
    SELECTEDVALUE ( 'TI Funnel Checkout'[FC] )
RETURN
    IF (
        currFC IN { "vs LY, vs Target" },
        SWITCH (
            SELECTEDMEASURENAME (),
            "Net order", [Net order],
            "Net revenue", [Net revenue],
            "Net units]", [Net units]
        )
    )

formula2 =
VAR currFC =
    SELECTEDVALUE ( 'TI Funnel Checkout'[FC] )
RETURN
    IF ( currFC IN { "vs LY, vs Target" }, SELECTEDMEASURE () )

Regards,

Xiaoxin Sheng