Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VikramAdi
Helper I
Helper I

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

Need to identify data whether live or not by child ,

 

Raw Data:

VikramAdi_0-1677841909234.png

 

Output:

VikramAdi_1-1677841970336.png

 

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.

1 ACCEPTED SOLUTION

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.

SamInogic_0-1678088361166.png

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/

View solution in original post

13 REPLIES 13
VikramAdi
Helper I
Helper I

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

VikramAdi_0-1678110394965.png

 

VikramAdi
Helper I
Helper I

Thanks @SamInogic  it's working..

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

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.

SamInogic_0-1678103595845.png


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/

VikramAdi
Helper I
Helper I

Currently acheved upto below screen. 

VikramAdi_1-1678038176495.png

 

But i want change font 

VikramAdi_0-1678038096712.png

 

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.

SamInogic_0-1678088361166.png

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/

@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

VikramAdi
Helper I
Helper I

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

@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?

VikramAdi
Helper I
Helper I

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

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

VikramAdi_0-1678031891489.png

 

@VikramAdi 

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

tamerj1
Super User
Super User

Hi @VikramAdi 

please try

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

VikramAdi
Helper I
Helper I

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

VikramAdi_1-1677865422720.png

 

 

SamInogic
Super User
Super User

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.

 

SamInogic_0-1677845756996.png

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.

SamInogic_1-1677845786621.png

 

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

SamInogic_2-1677845806433.png

 

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

SamInogic_3-1677845806436.png

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors