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

amitchandak's avatar
amitchandak
Super User
5 years ago

Saxon10 , You can create a color measure like the example given below and use that in conditional formatiing using field value option

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

 

Try like

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

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