topic DAX And Conditional Format in DAX Commands and Tips

DAX And Conditional Format

Anonymous — Mon, 20 Jun 2022 18:29:27 GMT

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?

Re: DAX And Conditional Format

tackytechtom — Mon, 20 Jun 2022 18:49:53 GMT

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/

Re: DAX And Conditional Format

Anonymous — Mon, 20 Jun 2022 19:07:18 GMT

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?

Re: DAX And Conditional Format

tackytechtom — Mon, 20 Jun 2022 19:12:35 GMT

Hi Anonymous ,

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/

Re: DAX And Conditional Format

Anonymous — Mon, 20 Jun 2022 19:19:54 GMT

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

Re: DAX And Conditional Format

Icey — Mon, 27 Jun 2022 07:59:59 GMT

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.