Matrix Conditional Formatting

[obriaincian]

Hi All,

I've a problem I'm struggling with.

I have 2 tables

Table 1

ID

11

22

33

44

Table 2

ID	Year
11	2021
22	2021
33	2022
44	2022
66	2023
77	2024
88	2024
99	2024
1010	2025
1111	2025

Matrix Visual (OUTPUT)

	2021	2022	2023	2024	2025
ID Count	2	2	1	3	2

What I'd like if the ID from ID count is in Table 1 I'd like to color the cell background green.

I tried to do this by going to "cell elements" and entering the below formula for the background color and where the result is >= 1 then color green.

measure = 
COUNTX(FILTER('Table1','Table1'[ID]= SELECTEDVALUE('Table2'[ID])),'Table1'[ID])

[v-cgao-msft]

Hi @obriaincian ,

Please try this:

Color = 
VAR _table = CALCULATETABLE(VALUES('Table2'[ID]))
VAR _color = IF(COUNTROWS(INTERSECT('Table1',_table))>0,"Green")
RETURN
_color

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[obriaincian]

Hi @v-cgao-msft,

Thank you for the solution. Unfortunately I'm getting the below error when using your code

Each table argument of 'INTERSECT' must have the same number of columns.

Any workaround for this?

[v-cgao-msft]

Hi @obriaincian ,

Please try:

Color = 
VAR _table = CALCULATETABLE(VALUES('Table2'[ID]))
VAR _color = IF(COUNTROWS(INTERSECT(DISTINCT('Table1'[ID]),_table))>0,"Green")
RETURN
_color

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Pragati11]

Hi @obriaincian ,

I don't think I understand your requirement. What do you mean by this statement of yours?

"What I'd like if the ID from ID count is in Table 1 I'd like to color the cell background green."

This statement doesn't make sense as the values in Table 1 are different (2 digit ID value) to what you have in your matrix visual. You matrix visual has a count of ID column not the actual value from ID column.

Kindly add more details to your requirement.