DAX And Conditional Format

[PrivateAnalytic]

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?

[tackytechtom]

Hi @PrivateAnalytic ,

I'd probably do something in this nature:

ConditionalFormattingMeasure = 
VAR minutes = ROUNDDOWN( MOD ( 'CallData'[AvgAnsTime], 3600 ) / 60, 0 )
RETURN 
IF ( minutes > 15, "#ff0000", BLANK() )

Does this solve the issue? 🙂

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

[PrivateAnalytic]

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 · Accepted solution]

Hi @PrivateAnalytic ,

// 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.

[tackytechtom]

Hi @PrivateAnalytic ,

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/

[PrivateAnalytic]

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?