Forum Discussion
Cross Reference Matrix
Hi, MSW
Please check the following methods.
Measure:
Color = Var _Countlocation =CALCULATE(DISTINCTCOUNT('Table'[Location]),ALLEXCEPT('Table','Table'[Customer ID])) Var _Countorder=CALCULATE(DISTINCTCOUNT('Table'[Order]),ALLEXCEPT('Table','Table'[Customer ID])) return IF(_Countlocation>1&&_Countorder>1,"Green",IF(_Countlocation>1,"Red",IF(_Countorder>1,"Blue")))Adds a color to each value in the list in conditional format.
Does this meet your desired outcome?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Of course, the table looks like this. The rows highlighted in red would show up in the matrix as they meet one of the criteria, a customer ordering the same item from multiple locations. The blue would be highlighted as it meets the criteria of ordering muliple types of items. Then the one in green would show up as it meets both criteria. *The overlap between the two criteria.
| Customer ID | Order | Item | Location |
| 123 | ABC | 4567 | USA |
| 123 | ABC | 4567 | China |
| 345 | DCB | 896 | USA |
| 345 | BNC | 896 | USA |
| 567 | NBA | 1090 | France |
| 567 | NBA | 1090 | France |
| 890 | LMN | 2087 | USA |
| 890 | LMN | 2087 | France |
| 890 | HCS | 8076 | Peru |
Hi, MSW
Please check the following methods.
Measure:
Color =
Var _Countlocation =CALCULATE(DISTINCTCOUNT('Table'[Location]),ALLEXCEPT('Table','Table'[Customer ID]))
Var _Countorder=CALCULATE(DISTINCTCOUNT('Table'[Order]),ALLEXCEPT('Table','Table'[Customer ID]))
return
IF(_Countlocation>1&&_Countorder>1,"Green",IF(_Countlocation>1,"Red",IF(_Countorder>1,"Blue")))
Adds a color to each value in the list in conditional format.
Does this meet your desired outcome?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.