Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

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?

 

 

 

 

4 REPLIES 4

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) 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.