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.