Forum Discussion

Picco's avatar
Picco
Frequent Visitor
1 year ago
Solved

Calculation Group and SelectedValue

Good day

 

I have 4 Calculation Items in my Calculation Group

 

MTD = CALCULATE(SELECTEDMEASURE(), DATESMTD('Date'[ADate]) )

MTD PY =  CALCULATE( SELECTEDMEASURE(), DATEADD(DATESMTD('Date'[ADate]),-1,YEAR))

Variance = 

VAR _NUMERATOR = CALCULATE(SELECTEDMEASURE(), DATESMTD('Date'[ADate]) )
VAR _DENOMINATOR = CALCULATE( SELECTEDMEASURE(), DATEADD(DATESMTD('Date'[ADate]),-1,YEAR)) 

VAR _VAR = DIVIDE( _NUMERATOR -_DENOMINATOR , _DENOMINATOR ,0)

return _var

Label = 

VAR _Value = CALCULATE( SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "MTD PY" )
VAR _VAR = CALCULATE( SELECTEDMEASURE(), CG_TimeIntelligence[Name] = "MTD VAR" )
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.9 , _DownArrow, IF( _VAR > 1, _UpArrow , "-" ))
&" "& FORMAT(_VAR, "PERCENT")


I want to add Conditional Formatting only on the LABEL Calculation Item, I create a measure 
Label Formatting = IF(SELECTEDVALUE(CG_TimeIntelligence[Name]) ="Label", 1 , blank() )

Then I get the error:  "Couldn't load the data for this visual" - cannot convert value 'Label' of type Text to type Numeric/Date.
SELECTEDVALUE works for MTD and MTD PY


What I've already tried:

  1. Changing the Label calculation Item to the actual calculations rather than referencing Calculation Items. That didn't work
  2. Removing the _Formatted Step
  3. I don't seem to say that Label is a STRING/ TEXT field in the calculation group properties
  4. Using MAX instead of SELECTEDVALUE, this didn't work either

I want to add the calculation group to the New card Visual, and format the Label red or green based on the variance <0 />0.

MTD and MTD PY should have no conditional formatting applied to it when you go to Call Out Values Font Colour and add Conditional Formatting. 


Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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.

7 Replies

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    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.

    • Picco's avatar
      Picco
      Frequent Visitor
      1 year ago

      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 measure

       

      VAR 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 following 
      IsLabel = 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 formatting




       
      • burakkaragoz's avatar
        burakkaragoz
        Super User
        1 year ago

        Picco ,

        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.

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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.

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi Picco,

    Thank you for using Microsoft Fabric Community Forum.
     

    I wanted to check if you had the opportunity to review the information provided by johnt75 . Please feel free to contact us if you have any further questions.

    Thank you.

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      1 year ago

      Hi Picco,

       

      We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

      If you need any further assistance, feel free to reach out.

       

      Thank you for being a valued member of the Microsoft Fabric Community Forum!

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        Hi Picco,

         

        As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.

        Thank you.