Forum Discussion

MSW's avatar
MSW
Helper I
4 years ago
Solved

Cross Reference Matrix

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. 

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

3 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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's avatar
      MSW
      Helper I
      4 years ago

      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 IDOrderItemLocation
      123ABC4567USA
      123ABC4567China
      345DCB896USA
      345BNC896USA
      567NBA1090France
      567NBA1090France
      890LMN2087USA
      890LMN2087France
      890HCS8076Peru
      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        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.