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 exa...
  • 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.

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.