topic Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO) in DAX Commands and Tips

Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Fri, 03 Mar 2023 11:19:07 GMT

Need to identify data whether live or not by child ,

Raw Data:

Output:

If live = yes,"Green"

IF live = no,"Red"

If live is combination of yes && NO then it's Orange".

Could you help me here dax to get 3 colors on coonditinal formating.

Note: slicer : date range from date dimension.

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

SamInogic — Fri, 03 Mar 2023 12:17:23 GMT

Hi VikramAdi ,

You can create a new column in your table for Live Colour with below DAX expression:

Live Colour = IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

), 'Table'[Live] ="no")) = CALCULATE(COUNTROWS('Table'), FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

)) , "Red",IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

), 'Table'[Live] ="yes")) = CALCULATE(COUNTROWS('Table'), FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

)),"Green","Orange"))

Please refer to the below screenshot for the same.

Now we have a colour based on Yes, No Live Values and will apply those colours to background colur for Live column so select Conditional formatting>> Background Colour option as shown in the below screenshot.

This will allow you to select column based on which the colour will be applied. Please refer to the below screenshot for the same.

Below will be the result you will see with background colour for Live Column,

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Fri, 03 Mar 2023 17:44:56 GMT

Thanks for your responce SamInogic , It's working fine. But Orange color font i want display as "Partial".

In live column, orange background coming like Yes, No, But required to display as "Partial".

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

tamerj1 — Fri, 03 Mar 2023 18:34:41 GMT

Hi VikramAdi

please try

FormatMeasure =
IF (
HASONEVALUE ( 'Table'[Live] ),
IF ( VALUES ( 'Table'[Live] ) = "Yes", "Green", "Red" ),
"Orange"
)

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Sun, 05 Mar 2023 15:59:28 GMT

tamerj1 your formula not working for both yes and No cases.

SamInogic your approach is fine but unable to get like below.

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

tamerj1 — Sun, 05 Mar 2023 16:34:05 GMT

VikramAdi

This is a measure not a calculated column. It should work.

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Sun, 05 Mar 2023 17:15:14 GMT

tamerj1 Yes, created a measure same. Yes and No (both) combination not working on above scenarios

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

tamerj1 — Sun, 05 Mar 2023 17:32:21 GMT

VikramAdi

Seems I'm misunderstanding or missing something. Would you please paste some screenshots of what you have done and achieve so far to clarify the picture?

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Sun, 05 Mar 2023 17:43:25 GMT

Currently acheved upto below screen.

But i want change font

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

tamerj1 — Sun, 05 Mar 2023 18:02:13 GMT

VikramAdi

Remove the Live column from the table and place the following measure

New Live =
IF (
HASONEVALUE ( 'Table'[Live] ),
VALUES ( 'Table'[Live] ),
"Partial"
)

then you can simply format the color based on the value

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

SamInogic — Mon, 06 Mar 2023 07:39:47 GMT

Hi VikramAdi,

You can create one custom column “Live Data” with below DAX expression:

Live Data = IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

), 'Table'[Live] ="no")) = CALCULATE(COUNTROWS('Table'), FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

)) , "No",IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

), 'Table'[Live] ="yes")) = CALCULATE(COUNTROWS('Table'), FILTER (

'Table',

'Table'[Parent] = EARLIER ('Table'[Parent])

&& 'Table'[Child] = EARLIER ( 'Table'[Child] )

)),"Yes","Partial"))

And apply conditional formatting for this column with Live Colour column as explained previously which will result as shown in below screenshot.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Mon, 06 Mar 2023 11:28:25 GMT

Thanks SamInogic it's working..

Having one doubt here, same concept how can we apply to the Matrix report ?

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

SamInogic — Mon, 06 Mar 2023 11:53:37 GMT

Hi VikramAdi ,

Did the Matrix Report means the Matrix Visual in Power BI Report? If yes then you can add the Matrix Visual with below configuration and same output will be there.

Let us know if you are expecting something different from this.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Re: Need DAX formula for Conditinal format on Yes,No and Both(Yes &NO)

VikramAdi — Mon, 06 Mar 2023 13:47:05 GMT

SamInogic When i add live data column in values filed it's picking First or last value. brand wise giving correct but when i'm looking at parent level, giving wrong output.

Also how did you mange without appear value for parent.