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

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
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