Cross Reference Matrix

[MSW]

Hello, 

Im a bit of a novice still to PBI. 

I am trying to figure out or know if its possible to create a formula/visual that can show me the cross reference between items in my data. For example, within my dataset I have customers, orders, location, items. What I am trying to find is if a customer is ordering from one or more locations (factory) and if they are ordering more than one type of item. I want each of those seperate on their own then ultimately finding the customer that intersects those two references too so I would end up with three outputs, which customers order from multiple locations, which customers order more than one type of item, and which customers both order more than one item from multiple locations. 

Perhaps a matrix view that can highlight these cross references? Or open to other ideas how to display the information.

Thanks. Appreciate any help you can provide. 

[daXtreme]

@MSW 

All of the above are possible in PBI/DAX. But... how you want to show it - up to you. If you want someone to build a prototype, you'll have to supply some data to tinker with and, what's important, some correct output you'd like to see from the data. So, we need input and output to build something that at least will be correct. Then you can think about how to use the created measures in your visuals.

[MSW]

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

[v-zhangti · Accepted solution]

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.