Conditional Formatting in visualization based on the multiple columns and criteria

[Saxon10]

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 Conditional Formatting in visualization based on the multiple columns and criteria. I would like to achieve my desired result by using visualization measure.

Data:

FRUIT LIST	AREA CODE	LANGUAGE	SALES CODE
Avocado	3100	EN	EU01
Avocado	3100	EN	US
Avocado	3100	EN	UK
Avocado	3200	EN	EU01
Avocado	3200	EN	US
Avocado	3200	EN	UK
Avocado	3300	EN	EU01
Avocado	3300	EN	US
Avocado	3300	EN	UK
Avocado	4500	EN	EU01
Avocado	4500	EN	US
Avocado	4500	EN	UK
Avocado	8900	EN	EU01
Avocado	8900	EN	US
Avocado	8900	EN	UK
Star Fruit	WEST	EN	INDIA01
Star Fruit	WEST	EN	INDIA02
Star Fruit	WEST	EN	NA
Star Fruit	NORTH	EN	INDIA01
Star Fruit	NORTH	EN	INDIA02
Star Fruit	NORTH	EN	NA
Star Fruit	EAST	EN	INDIA01
Star Fruit	EAST	EN	INDIA02
Star Fruit	EAST	EN	NA
Star Fruit	SOUTH	EN	INDIA01
Star Fruit	SOUTH	EN	INDIA02
Star Fruit	SOUTH	EN	NA
Banana	KP1	EN	XX1
Banana	KP1	EN	NA
Banana	KP1	EN	NA
Banana	PP1	EN	XX2
Banana	PP1	EN	NA
Banana	PP1	EN	NA
Apple	444	EN	BAN03
Apple	444	EN	NA
Apple	444	EN	NA
Orange	555	EN	BAN03
Orange	555	EN	NA
Orange	555	EN	NA
Orange	555	EN	BAN04
Orange	555	EN	NA
Orange	555	EN	NA
Orange	555	EN	BAN05
Orange	555	EN	NA
Orange	555	EN	NA
Plum	123	EN	BAN03
Plum	123	EN	NA
Plum	123	EN	NA
Watermelon		EN	EU01
Watermelon		EN	US
Watermelon		EN	UK

Report:

ITEM	FRUIT LIST	AREA CODE	LANGUAGE	SALES CODE
123	Avocado	3100	EN	EU01
124	Avocado	3100	EN	US
125	Avocado	3100	EN	UK
126	Avocado	WEST	EN	UK
127	Avocado	3100	EN	UK
128	Avocado	3100	EN	UK
129	Avocado	3100		UK
130	Avocado	3100	EN	INDIA01
131	Avocado	3100	EN
234	Star Fruit	WEST	EN	INDIA01
235	Star Fruit	NORTH	EN	INDIA01
236	Star Fruit	EAST	EN	INDIA01
237	Star Fruit	SOUTH	EN	INDIA01
238	Star Fruit	WEST	EN	INDIA02
239	Star Fruit	NORTH	EN	INDIA02
240	Star Fruit	EAST	EN	INDIA02
241	Star Fruit	SOUTH	EN	INDIA02
56	Watermelon		EN	UK

RUELS:

Fruits List:

In report Table the item 123 Fruits list is “Avocado” and it’s matched according to the Data Table.    

The area Code should be matched according to the Date Table.

Area Code:

In report Table item 126 Fruits list “Avocado” area code is “ West” but it’s not matched according to the Data Table.    

In Data table the fruits list “Avocado” area code pertaining to 3100, 3200, 3300, 4500 and 8900.

The area Code match criteria with following combination columns are FRUIST LIST + AREA CODE

Language:

The language column can’t be blanks and must be “EN” in Report Table.

Sales Code

In report Table item 130 Fruits list “Avocado” area code is “3100” and sales code is “India01” but it’s not matched according to the Data Table.    

In Data table the fruit list “Avocado” with area code  “3100, 3200, 3300, 4500,8900” and sales code UK, US, EU01.  

The Sales Code match criteria with following combination columns are FRUIST LIST + AREA CODE + SALES CODE

Result:

If not matched then I would like to highlighted the error in “Orange colour” and If it’s matched then I would like to highlighted “Green colour” in Report Table.

Result and Data snapshot.

[Saxon10]

Hi

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 the thoughts.  

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.

3. Can you please explain if it possibile about your logic and selected value logic?

[amitchandak]

@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-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Saxon10]

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 · Accepted solution]

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]

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

[Saxon10]

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