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
Juuls
Frequent Visitor

Conditional format varchar

Hi all,

 

I am trying to apply condtional formatting to my table, which consists of varchar data. I cannot modify the data, since I am using DirectQuery. In order to get it working, I have tried to work with a lookup table, which creates dummies; however, this didn't give me the result I was looking for. The column gets one and the same color, regardless of the settings I have applied (color, rule, sum etc.).

Below I firstly show the table and values I am using in the original one and after that the lookup table. Is my approach even possbile? If so, could someone please help me how to configure this? Help is much appreciated, thanks in advance!

 

SourceRun_status
Source a1
Source b2
Source c8
Source d11
Source eB
Source fE
Source gF

 

Run_StatusValue
11
21
112
E2
F2
B3
83

 

The result I am looking for is as follows in terms of colors:

1 = green

2 = red

3 = blue

 

1 ACCEPTED SOLUTION

Hello @Juuls ,

This is related to the locale, replace all ";" (semicolon) by "," (comma) everything should work fine.

Check the corrected measurement below:

CondittionalFormatting = 
VAR Temp_table =
    ADDCOLUMNS (
        SUMMARIZE (
            Run_Status,
            Run_Status[Source],
            Run_Status[Run_Status]
        ),
        "@Status_Values", LOOKUPVALUE ( Lookup[Value], Lookup[Run_Status], Run_Status[Run_Status] )
    )
RETURN
    SWITCH ( MAXX ( Temp_table, [@Status_Values] ), 1 , "Green" , 2 , "Red" , 3 , "Blue" )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @Juuls ,

 

Try the following code:

 

CondittionalFormatting = 
VAR Temp_table =
    ADDCOLUMNS (
        SUMMARIZE (
            Run_Status;
            Run_Status[Source];
            Run_Status[Run_Status]
        );
        "@Status_Values"; LOOKUPVALUE ( Lookup[Value]; Lookup[Run_Status]; Run_Status[Run_Status] )
    )
RETURN
    SWITCH ( MAXX ( Temp_table; [@Status_Values] ); 1; "Green"; 2; "Red"; 3; "Blue" )

MFelix_0-1603463815639.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Juuls
Frequent Visitor

Hi @MFelix ,

Firstly, thank you very much for your reply, much appreciated!

Secondly maybe a slightly stupid question; I have tried your solution, but I keep getting a syntax error near ';'.

I have tried generating a measure, column and table with the code without success unfortunately.

I probably am doing something wrong, but since I'm rather new to this I hope you can tell me how.

Thanks once more!

Regards,

Juul

Hello @Juuls ,

This is related to the locale, replace all ";" (semicolon) by "," (comma) everything should work fine.

Check the corrected measurement below:

CondittionalFormatting = 
VAR Temp_table =
    ADDCOLUMNS (
        SUMMARIZE (
            Run_Status,
            Run_Status[Source],
            Run_Status[Run_Status]
        ),
        "@Status_Values", LOOKUPVALUE ( Lookup[Value], Lookup[Run_Status], Run_Status[Run_Status] )
    )
RETURN
    SWITCH ( MAXX ( Temp_table, [@Status_Values] ), 1 , "Green" , 2 , "Red" , 3 , "Blue" )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Juuls
Frequent Visitor

Hi @MFelix

This works fine indeed, thank you very much.

Enjoy your weekend!

Regards,

Juul

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.