cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Icon Conditional Formatting and Calculation Groups using Field Values

I've just come across the icon conditional formatting and calculation groups. When I was trying to combine them to reduce the time needed in doing the conditional formatting on each columns I thought I'd give it a go to apply it through calculation groups.

So first I tested out creating a measure for doing the CF to test doing it without calculation groups and it worked fine.

FieldCF = SWITCH(TRUE(),
[Field to format] = 955000000, "CircleHigh",
[Field to format] = 955000001, "CircleMedium",
[Field to format] = 955000002, "CircleLow",
"4CircleLow"
)
This worked like a charm. So then I went and created a dummy record of the field I wanted adjusted for a calculated group to tweak and tested the resultant output in a table to make sure it was outputting the correct values for the Conditional Formatting. So far was looking great. Calculated Group looked like the following:

FieldCFs = VAR _CF = SWITCH(TRUE(),
SELECTEDMEASURE() = 955000000, "CircleHigh",
SELECTEDMEASURE() = 955000001, "CircleMedium",
SELECTEDMEASURE() = 955000002, "CircleLow",
"4CircleLow"
)
RETURN
IF(CONTAINSSTRING(SELECTEDMEASURENAME(), "Field*CF"), _CF, SELECTEDMEASURE())

Then I went to apply the icon conditional formatting using the dummy measure tweaked by the calculated group, thinking I had everything solved but it won't allow me to apply the dummy measure that the calculated group is tweaking as a "What field should we base this on" when using "Field Value".

Any idea on why this is? Is it an order of operations? Like is conditional formatting applied before a calculated group is applied and so it's recognising that the values that the calculated group would modify the column to be? It doesn't give any errors, it just doesn't allow it's selection so it's kind of difficult to troubleshoot.

EDIT: Just thought I should note because I didn't make it clear. I am trying to apply the conditional formatting to a measure. So it's an explicit aggregration for both the CF measure and the measure it's being applied to. Just to rule out the implicit complications you can get when using Calculated Groups.
1 ACCEPTED SOLUTION
Frequent Visitor

Ok I think I have discovered the root cause.

Because the underlying measure is originally of a type that is Integer, it is not adjusting to the type String that the Calculation Group is adjusting it to. And a field value source requires a measure to have a type of String.

So if I convert the measure to output a string initially and then do the switch on that through the Calculation Group it is now selectable.

So the problem is that the measure maintains it's original Data Type even if the Calculation Group is changing the output.

10 REPLIES 10
Frequent Visitor

Ok I think I have discovered the root cause.

Because the underlying measure is originally of a type that is Integer, it is not adjusting to the type String that the Calculation Group is adjusting it to. And a field value source requires a measure to have a type of String.

So if I convert the measure to output a string initially and then do the switch on that through the Calculation Group it is now selectable.

So the problem is that the measure maintains it's original Data Type even if the Calculation Group is changing the output.

Super User

Hi @Ashes ,

Sorry for missreading your request, glad that you were abble to figure out what was going on.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

Hi @Ashes ,

When you mix calculation groups with condittional formatting everything works differently.

Check the videos below for explanation on what is happening and how to solve it.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

Thanks for the vids. Unfortunately they didn't seem to address the issue I am experiencing, where I am unable to select a measure tweaked by the Calculated Group for a Field Value option for Icon Conditional Formatting, but is selectable as a Rule input to the Icon Conditional Formatting (just can't be used there because it doesn't contain values).

Effort to provide them appreciated though.

Super User

Hi @Ashes ,

If the information is sensitive please share it trough private message.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

New Member

That's totally fine. I'm greatful for trying to assist me.

I'm pretty sure your attempt at helping me is what assisted me in figuring it out. Sounding it out with someone always helps 🙂

New Member

I'm sure I replied to this from this account. But I'll try again

Example Power BI File

So I had a full explanation of exactly the steps but I'll just summarize this time due to time. There are two Icon Conditional Format Source Measures created. One is modified by the Calculation Group which is KPI_CF_CG, it can't seem to be used a source for a Field Value while the other more specific measure doing the switch itself works just fine. Behaviour seems to be the same if it is a natural column or a display measure that is it is applied to.

Super User

Hi @Aklys ,

I believe that you need to have this formatting to be done automatticaly based on the measure that you have so you don't do any condittional formatting in the values, since the condittional formatting is done using a different process where you can select each of the values, having this on the calculation item does not avoid you to do it manually for each measure.

In this case and since your are doing just the normal icons I would do the KPI's calculation using the following way:

``````VAR _CF =
SWITCH (
TRUE (),
SELECTEDMEASURE () = 9000, UNICHAR(128994),-- "CircleHigh",
SELECTEDMEASURE () = 9001, UNICHAR ( 128993 ),-- "CircleMedium",
SELECTEDMEASURE () = 9002, UNICHAR ( 128308 ),-- "CircleLow",
UNICHAR ( 9899 )
)
RETURN
IF (
CONTAINSSTRING ( SELECTEDMEASURENAME (), "KPI*CF*CG" ),

SELECTEDMEASURE() & " " &_CF,
SELECTEDMEASURE ()
)``````

This will add the KPI on the measure it self.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

This unfortunately doesn't resolve the issue I'm mentioning. I get you can use unichar instead but that solution comes with it's own problems. Such as it creates inconsistency of application throughout reports that use conditional formatting already for other things. Also the UNICHAR images are too different to align with other uses of the icon conditional formatting.

I wasn't looking for an alternative solution to applying the images, I was more trying to understand why that conditional formatting method doesn't work, this way I will know the limitations better of Calculation Groups. So I was wondering if any one knows if it's an order of operations or something else causing the experience.

Frequent Visitor

It seems I can't share files from where I am employed. So I'll create an example of the scenario with some mockup data under my personal account later on.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors