Forum Discussion

Sasha's avatar
Sasha
Helper III
7 years ago
Solved

Comparing field to itself

I have the following data about ownership of barbers in three barber shops

enter image description here

I'm interested presenting a matrix on PBI where the rows are barbers that are owners in at least one barber shop and the columns are all barbers. Each cell in the matrix to represent relationship between the barbers when they're owners and the rest of the barbers. If two barbers own a barbershop their relationship is "owners" even if other barber shop one of them is tenant. If the second barber is always a tenant in owner's barber shop than it's a owner-tenant relationship. Same barber is "same person". Any other relationship remains blank.

I expect the following result:

enter image description here

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Sasha ,

     

    Drag [Barber] from Table2 to Rows and add measure below.

    Table2 =
FILTER ( Table1, Table1[Status] = "Owner" )

    Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Table2[Barber] ) = SELECTEDVALUE ( Table1[Barber] ), "Same Person",
    NOT (
        ISEMPTY (
            FILTER (
                Table1,
                Table1[BarberShop] IN VALUES ( Table2[BarberShop] )
                    && Table1[Status] = "Owner"
            )
        )
    ), "Owners",
    NOT (
        ISEMPTY (
            FILTER ( Table1, Table1[BarberShop] IN VALUES ( Table2[BarberShop] ) )
        )
    ), "Owner-Tenant"
)

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    Sasha ,

     

    Drag [Barber] from Table2 to Rows and add measure below.

    Table2 =
FILTER ( Table1, Table1[Status] = "Owner" )

    Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Table2[Barber] ) = SELECTEDVALUE ( Table1[Barber] ), "Same Person",
    NOT (
        ISEMPTY (
            FILTER (
                Table1,
                Table1[BarberShop] IN VALUES ( Table2[BarberShop] )
                    && Table1[Status] = "Owner"
            )
        )
    ), "Owners",
    NOT (
        ISEMPTY (
            FILTER ( Table1, Table1[BarberShop] IN VALUES ( Table2[BarberShop] ) )
        )
    ), "Owner-Tenant"
)
    • Sasha's avatar
      Sasha
      Helper III
      7 years ago

      v-chuncz-msft  how can I sort the columns of the output in my example so all the the barbers that have "the same" person will appear first similarly to the order they appear in the rows and only after that the rest barbers (that basically only rent) will appear in the columns. In this case, the diagonal will be continuous and this will obviously be more pleasing visually.