Forum Discussion

Juuls's avatar
Juuls
Frequent Visitor
5 years ago
Solved

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

 

Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 years ago

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

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

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

     

     

    • Juuls's avatar
      Juuls
      Frequent Visitor
      5 years ago

      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

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

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