Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
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
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?
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |