Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Compare Names between tables

Hello All,  I have 2 tables with Names of People.  Table 1 Name  John Max Marry Hilton Mark Chris Leo Table 2 Name John Max Lee Daniel Oleg Swetha both of the tables contai...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous

     

    What is your expected output in the table visual? If you only want to know which names only exist in Table 1 and which names only exist in Table 2, we just need to compare name values in Name column of each table and don't need to use other columns. 

     

    One idea is to add a column in each table to mark "Yes" if a name is only in this table and "No" if it's in the other table too. You can use this column to filter visuals later. 

    Only in Table 1 = IF('Table 1'[Name] IN VALUES('Table 2'[Name]),"No","Yes")


Only in Table 2 = IF('Table 2'[Name] IN VALUES('Table 1'[Name]),"No","Yes")

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

     

     

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous

 

What is your expected output in the table visual? If you only want to know which names only exist in Table 1 and which names only exist in Table 2, we just need to compare name values in Name column of each table and don't need to use other columns. 

 

One idea is to add a column in each table to mark "Yes" if a name is only in this table and "No" if it's in the other table too. You can use this column to filter visuals later. 

Only in Table 1 = IF('Table 1'[Name] IN VALUES('Table 2'[Name]),"No","Yes")


Only in Table 2 = IF('Table 2'[Name] IN VALUES('Table 1'[Name]),"No","Yes")

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi v-jingzhang,

     

    I think I should elaborate. 

    I have attached some example Data Below.

     

    File 1

     

     

    File 2

     

     

     

     


    I want to achieve 2 Table Visuals on a Page.

     

    1st will show the names that appear in File 1 but are missing in File 2

    2nd will show the names that appear in File 2 but are missing in File 1

     

    When achieved correctly 

    1st Table Visual should give the following names

    Lana

    Mark

    Marcus

    Dominik

    John

     

    2nd Table Visual should give the following names

    Phil

    Susan

    Lee

    Dana

    David

    Anna

     

    I hope this helps.

     

    Thank You for your help

     

    BR,

    Kris

    • v-jingzhang's avatar
      v-jingzhang
      Community Support
      4 years ago

      Hi Anonymous 

       

      Sorry the two images you attached are the same...

       

      I think my method still works. You just need to add two table visuals on a report page. In the first table visual, put Name from Table 1 into it as Values. Drag Only in Table 1 column to Filters on this visual section in Filter pane and set show items when value is "Yes". Repeat the same operation for the second visual for Table 2. 

       

      Tour the report Filters pane - Power BI | Microsoft Docs

       

      BR,

      Jing

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank You, It worked indeed!

  • Tarif's avatar
    Tarif
    New Member
    1 year ago

    how to make it in Measure

    Thanks