Forum Discussion

mpml's avatar
mpml
Frequent Visitor
5 years ago
Solved

Help Please - Table with two columns referencing same column in another table...

I have one table that looks like this:   TABLE 1 ID | NAME | DATE   Another table that looks like this: TABLE 2 ID | NUMBER | RELATED_ID   I want to be able to select NAME in a slicer fr...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, mpml 

    I am not sure if I understood your question correctly.

     

    Please check the below picture and the sample pbix file's link down below.

    I created a sample pbix file. And I thought it would be great if I could create an extended version of the above sample.

    I could not find a way to sort the names by number column in Table 2. So I created one more measure, which is a [Name Sort]. And I inserted it into the table visualization. And made the width almost zero not to show on the visualization.

     

     

    Name Measure =
    CALCULATE (
    SELECTEDVALUE ( Table1[Name] ),
    FILTER (
    Table1,
    Table1[ID]
    IN CALCULATETABLE (
    VALUES ( Table2[Related_ID] ),
    FILTER ( Table2, Table2[ID] = SELECTEDVALUE ( Slicer[ID] ) )
    )
    )
    )
     
    Name Order =
    CALCULATE (
    MAX ( Table2[Number] ),
    TREATAS ( VALUES ( Table1[ID] ), Table2[Related_ID] ),
    FILTER ( Table2, Table2[ID] = MAX ( Slicer[ID] ) )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

amitchandak's avatar
amitchandak
Super User
5 years ago

mpml , Not able to get the logic how you got mike and Jack with John filter. Please explain

mpml's avatar
mpml
Frequent Visitor
5 years ago

So Table 2 is basically a relationship table. Table 1 is just a meta table that contains information about each ID. 

 

Table 2 has two ID columns both referencing Table 1. I want to be able to select one Name from Table 1 that then causes all the Names to surface from Table 2's Related_ID column.

 

Imagine it was a dating app and we were storing all the names someone swiped right on. I'd want to be able to filter to JOHN and see all the people he swiped right on. Both ID columns relate back to the table that has the NAME column.

 

Table 1 would be our user information table. Table 2 would be our record of how John and everyone else Swiped. The first ID column would be the Name of who swiped and then the second ID column would be people they swiped right on. Both columns tie back to our user database.