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]) 
            )
      )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Richard, 

 

No, they've all got the same three (but different values for each metric) which i think could be the problem! 

 

Is there any way around this?

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