Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

Conditional Formatting in visualization based on the multiple columns and criteria

  Hi,   I have a two tables one is Data and another one os Report. Both tables contain the following columns are Fruits List, Area Code, Language, Sales Code.     I am trying to make a Condition...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Saxon10 ,

     

    Looking at the data you have and taking into account that you need several formattings to different columns I did the following:

     

    • Added an ID column to each of the tables:
    ID = Report[FRUIT LIST] & Report[AREA CODE]&Report[SALES CODE]
ID = Data[FRUIT LIST]&Data[AREA CODE]&Data[SALES CODE]
    • Hide the column in the report view
    • Created the following 4 measures:
    FORMATTING FRUITLIST = 
    IF (
        LOOKUPVALUE (
            Data[FRUIT LIST];
            Data[FRUIT LIST]; SELECTEDVALUE ( Report[FRUIT LIST] )
            
        ) = BLANK();
        "Orange";
        "Green"
    )

FORMATTING AREACODE = 
IF (
    LOOKUPVALUE ( Data[ID]; Data[ID]; MAX ( Report[ID] ) ) = BLANK ();
    IF (
        LOOKUPVALUE (
            Data[AREA CODE];
            Data[AREA CODE]; SELECTEDVALUE ( Report[AREA CODE] );
            Data[FRUIT LIST]; SELECTEDVALUE ( Report[FRUIT LIST] )
        ) = BLANK();
        "Orange"
        
    )
    
)

FORMATTING LANGUAGE = IF(SELECTEDVALUE(Report[LANGUAGE]) = BLANK(); "Orange")

FORMATTING SALESCODE = 
   
IF (
    LOOKUPVALUE ( Data[ID]; Data[ID]; MAX ( Report[ID] ) ) = BLANK ();
    IF (
        LOOKUPVALUE (
            Data[SALES CODE];
            Data[SALES CODE]; SELECTEDVALUE ( Report[SALES CODE] );
            Data[FRUIT LIST]; SELECTEDVALUE ( Report[FRUIT LIST] )
        ) = BLANK();
        "Orange"
        
    )
    
)

    Now I use this measure to condittional format each of the columns in the visualization:

     

    You can change the colours by a format string similar to "#000000" and use the HEX codes.

     

    PBIX file attach (PBI December version).

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago

Thanks for your reply. Your measure not working.

I don't think I need a sum function here becuase trying to match from one table to another table via conditional formating. 

Can you please help me what is the actual measure according to my query.

 

MFelix's avatar
MFelix
Super User
5 years ago

Hi Saxon10 ,

 

Looking at the data you have and taking into account that you need several formattings to different columns I did the following:

 

  • Added an ID column to each of the tables:
ID = Report[FRUIT LIST] & Report[AREA CODE]&Report[SALES CODE]
ID = Data[FRUIT LIST]&Data[AREA CODE]&Data[SALES CODE]
  • Hide the column in the report view
  • Created the following 4 measures:
FORMATTING FRUITLIST = 
    IF (
        LOOKUPVALUE (
            Data[FRUIT LIST];
            Data[FRUIT LIST]; SELECTEDVALUE ( Report[FRUIT LIST] )
            
        ) = BLANK();
        "Orange";
        "Green"
    )

FORMATTING AREACODE = 
IF (
    LOOKUPVALUE ( Data[ID]; Data[ID]; MAX ( Report[ID] ) ) = BLANK ();
    IF (
        LOOKUPVALUE (
            Data[AREA CODE];
            Data[AREA CODE]; SELECTEDVALUE ( Report[AREA CODE] );
            Data[FRUIT LIST]; SELECTEDVALUE ( Report[FRUIT LIST] )
        ) = BLANK();
        "Orange"
        
    )
    
)

FORMATTING LANGUAGE = IF(SELECTEDVALUE(Report[LANGUAGE]) = BLANK(); "Orange")

FORMATTING SALESCODE = 
   
IF (
    LOOKUPVALUE ( Data[ID]; Data[ID]; MAX ( Report[ID] ) ) = BLANK ();
    IF (
        LOOKUPVALUE (
            Data[SALES CODE];
            Data[SALES CODE]; SELECTEDVALUE ( Report[SALES CODE] );
            Data[FRUIT LIST]; SELECTEDVALUE ( Report[FRUIT LIST] )
        ) = BLANK();
        "Orange"
        
    )
    
)

Now I use this measure to condittional format each of the columns in the visualization:

 

You can change the colours by a format string similar to "#000000" and use the HEX codes.

 

PBIX file attach (PBI December version).

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    thank you so much for your quick action. I will check and come back you. 

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    Thanks you so much for your prompt action about my thread.

    I like way of your approach. Can you please help me regarding my question.

     

    1. Did you added area code with fruits list combination for "FORMATTING AREACODE" measure? I can see the fruits list on your measure but just clarifying my thoughts.

     

    FORMATTING AREACODE = IF ( LOOKUPVALUE ( Data[ID], Data[ID], MAX ( Report[ID] ) ) = BLANK (), IF ( LOOKUPVALUE ( Data[AREA CODE], Data[AREA CODE], SELECTEDVALUE ( Report[AREA CODE] ), Data[FRUIT LIST], SELECTEDVALUE ( Report[FRUIT LIST] ) ) = BLANK(), "Orange" ) )

     

     

    2. How can I added "area code" in exciting measure because the sales code matching logic/criteria is FRUIST LIST + AREA CODE + SALES CODE. If we try to match the area code and sales code maybe the results went wrong.

     

    FORMATTING SALESCODE = IF ( LOOKUPVALUE ( Data[ID], Data[ID], MAX ( Report[ID] ) ) = BLANK (), IF ( LOOKUPVALUE ( Data[SALES CODE], Data[SALES CODE], SELECTEDVALUE ( Report[SALES CODE] ), Data[FRUIT LIST], SELECTEDVALUE ( Report[FRUIT LIST] ) ) = BLANK(), "Orange" ) )