Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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:
ID = Report[FRUIT LIST] & Report[AREA CODE]&Report[SALES CODE]
ID = Data[FRUIT LIST]&Data[AREA CODE]&Data[SALES CODE]
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).
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
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.
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?
@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
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.
Hi @Saxon10 ,
Looking at the data you have and taking into account that you need several formattings to different columns I did the following:
ID = Report[FRUIT LIST] & Report[AREA CODE]&Report[SALES CODE]
ID = Data[FRUIT LIST]&Data[AREA CODE]&Data[SALES CODE]
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).
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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" ) )
thank you so much for your quick action. I will check and come back you.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |