Forum Discussion
Calculation Group and SelectedValue
Calculation group logic is only applied once per measure, so statements like
VAR _Value = CALCULATE( SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "MTD PY" )won't work.
What you could do is to create a separate calculation group for labels, which would then be able to call the time intelligence calculation items. You could have the calculation item return a numeric value for the variance, which you would then be able to detect in conditional formatting, and use the format string property of the label calculation item to return the formatted string using the exact code you already have in the Label calculation item you posted.
Hi Picco ,
This is a tricky issue with calculation groups and conditional formatting. The problem is that your "Label" calculation item returns a text string (with formatting and arrows), but conditional formatting expects numeric values.
The issue: Your Label item returns formatted text like "PY: $123M ↑ 15%", but conditional formatting needs a numeric value to work with.
Solution - Create a separate numeric measure for formatting:
Label Formatting =
VAR IsLabel = SELECTEDVALUE(CG_TimeIntelligence[Name]) = "Label"
VAR Variance = CALCULATE(SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "Variance")
RETURN
IF(
IsLabel,
IF(Variance > 0, 1, IF(Variance < 0, -1, 0)),
BLANK()
)Alternative approach - split your Label item:
Instead of one Label item that returns formatted text, create two separate items:
// Label Value (returns numeric for formatting)
Label Value =
VAR Variance = CALCULATE(SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "Variance")
RETURN Variance
// Label Display (returns formatted text)
Label Display =
VAR Value = CALCULATE(SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "MTD PY")
VAR Var = CALCULATE(SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "Variance")
VAR UpArrow = UNICHAR(9650)
VAR DownArrow = UNICHAR(9660)
VAR Formatted = IF(CONTAINSSTRING(SELECTEDMEASURENAME(), "Value"),
FORMAT(Value, "$# ###,,M"),
FORMAT(Value, "# ###"))
RETURN
"PY: " & Formatted & " " &
IF(Var < 0, DownArrow, IF(Var > 0, UpArrow, "-")) & " " &
FORMAT(Var, "PERCENT")Then use "Label Value" for conditional formatting rules and "Label Display" for the actual display.
For the conditional formatting:
- Use your numeric formatting measure
- Set rule: if value > 0 then green, if value < 0 then red
This separates the display logic from the formatting logic, which is what conditional formatting needs.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
Thank you for your response burakkaragoz
I've tried both methods and none gives me what I want at the end of the day
The issue with the following measureVAR IsLabel = SELECTEDVALUE(CG_TimeIntelligence[Name]) = "LABEL" VAR _Variance = CALCULATE(SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "MTD VAR") RETURN IF( IsLabel, IF(_Variance > 0, 1, IF(_Variance < 0, -1, 0)), BLANK() )Is this portion
var IsLabel = SELECTEDVALUE(CG_TimeIntelligence[Name]) = "LABEL"You would expect that if you do the followingIsLabel = SELECTEDVALUE(CG_TimeIntelligence[Name]) you will get Label if Label is selected in a filter, MTD if MTD is selected etc.I don't know why I don't get the CalculationItem Name back when I choose LABEL but some portion of the output.
I've tried it with the CalculationItem[Ordinal] as well and get the same result.
If I can "isolate" that this is my Label if that is true then apply the following rules in terms of the colour that will be based off my Variance which will be numeric for the conditional formattingPicco ,
You've found the exact issue! SELECTEDVALUE is getting confused because your LABEL calculation item returns formatted text, not just the item name.
Quick fixes to try:
Use the ordinal instead of name:
Label Formatting = VAR IsLabel = SELECTEDVALUE(CG_TimeIntelligence[Ordinal]) = 4 // Check what number LABEL is VAR Variance = CALCULATE(SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "MTD VAR") RETURN IF(IsLabel, IF(Variance > 0, 1, IF(Variance < 0, -1, 0)), BLANK())
Or try MAX instead:
VAR IsLabel = MAX(CG_TimeIntelligence[Name]) = "LABEL"
The ordinal approach usually works better because it's numeric and doesn't get messed up by whatever complex stuff your calculation items are outputting.
What ordinal number does your LABEL item show in the calculation group? Use that number instead of trying to match the text.
This is one of those annoying calculation group quirks where SELECTEDVALUE doesn't behave like you'd expect when the items return complex formatted results.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
