Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX And Conditional Format

Hello all! I created a calculation which displays the Average Time it takes for someone to answer the phone:

Avg Ans Time (Mins) =
VAR hours =
ROUNDDOWN( 'CallData'[AvgAnsTime] / 3600,0)
VAR minutes =
ROUNDDOWN( MOD( 'CallData'[AvgAnsTime], 3600) / 60, 0)
VAR seconds =
INT( MOD ('CallData'[AvgAnsTime], 60) )
RETURN
FORMAT(hours, "0") & ":"
& FORMAT(minutes, "00") & ":"
& FORMAT(seconds, "00")
 
I put the values on a table, and I am looking to conditionally format the values to where anything above 15 minutes is red. However, I am not able to do that as Power BI blurs out the calculation from being conditionally formatted. Is there still a way I can conditionally format this?
  • Icey's avatar
    Icey
    4 years ago

    Hi Anonymous ,

     

    // Calculation error in measure 'CallData'[Conditional Format]: Cannot convert value "::" of type Text to Type number.

     

    Please use 'CallData'[AvgAnsTime] instead of 'CallData'[Avg Ans Time (Mins)]. 

    'CallData'[AvgAnsTime] return a number value but 'CallData'[Avg Ans Time (Mins)] returns a text value, which can't be used in Maths functions.

     

    Try this:

    color =
IF ( 'CallData'[AvgAnsTime] > ( 15 * 60 ), "#ff0000")

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    Hi Anonymous ,

     

    I think it is greyed out because you are using the FORMAT function, which Power BI translates to TEXT. I suggest you create an additional "conditional formatting" measure with the desired logic. You could use parts of your measure and wrap it in an if clause where you return 1 if minutes >= 15 else 0. That measure, you should be able to use it in the conditional formatting pane. Also, you could directly color code in the DAX measure, since you need to create a new measure anyway:

    Apply conditional table formatting in Power BI - Power BI | Microsoft Docs

     

    Let me know if this helps 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello Tom! So I am a little confused. I am with the desired logic. Would it be like:

      Conditional Format =

      VAR color = IF('CallData'[Avg Ans Time (Mins)] > "15", "#ff0000", BLANK())

       

      Or would there be more to it?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately not, I did the formula using the columns it says it can not display the visual. (Calculation error in measure 'CallData'[Conditional Format]: Cannot convert value "::" of type Text to Type number.

       

      I also tried something else, but instead of conditionally formatting it, it instead just put "#ff0000" as the record in the table instead of actually coloring it

      • Icey's avatar
        Icey
        Community Support
        4 years ago

        Hi Anonymous ,

         

        // Calculation error in measure 'CallData'[Conditional Format]: Cannot convert value "::" of type Text to Type number.

         

        Please use 'CallData'[AvgAnsTime] instead of 'CallData'[Avg Ans Time (Mins)]. 

        'CallData'[AvgAnsTime] return a number value but 'CallData'[Avg Ans Time (Mins)] returns a text value, which can't be used in Maths functions.

         

        Try this:

        color =
IF ( 'CallData'[AvgAnsTime] > ( 15 * 60 ), "#ff0000")

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.