Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Relationship between tables

Hi,    I'm having some trouble creating a relationship between 3 tables, which are all related by the column 'metric'. I'd love to link them all so i can then filter/slice by the 'Metric' - Each ta...
Need Help
Tips and Tricks
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    This DAX might help create a table that you can then relate your three existing tables to

     

    Table = 
    DISTINCT(
        UNION(
            VALUES('Demo'[METRIC]) ,
            VALUES('Country'[METRIC]) ,
            VALUES('City'[METRIC]) 
            )
      )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Maybe create a new table that has every METRIC value

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

This DAX might help create a table that you can then relate your three existing tables to

 

Table = 
    DISTINCT(
        UNION(
            VALUES('Demo'[METRIC]) ,
            VALUES('Country'[METRIC]) ,
            VALUES('City'[METRIC]) 
            )
      )
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Phil, 

     

    That worked! 

     

    Thank you so much :) 

     

    Sonia

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Phil_Seamark

       

      Thanks again for this great tip, I've been using it a lot!

       

      Unfortunately, for this latest one, I'm getting a null value in the new table, but I can't work out why... 

       

      Any ideas?

       

       

      Account Relationship =
      DISTINCT(
      UNION(
      VALUES('HOMEPAGE'[SITE SECTION]) ,
      VALUES('HOMEPAGE+FRANCHISE'[SITE SECTION]) ,
      VALUES('MARVEL'[SITE SECTION]),values('OTHERS'[SITE SECTION]),values('PIXAR'[SITE SECTION]),values('SHOP ALL'[SITE SECTION]),values('STAR WARS'[SITE SECTION])
      )
      )

       

      Then in my new Table it returns

       

      HOMEPAGE

       

      HOMEPAGE+FRANCHISE

      MARVEL

      OTHERS

      PIXAR

      SHOP ALL

      STAR WARS

       

      Thanks again

       

      Sonia