Power BI Compares Two Columns in Different Tables

If you don’t know how to compare two columns in different tables in Power BI, then this Power BI Tutorial might help you to solve it. This Power BI Tutorial explains how we can compare two columns in Power BI. Also, covered the below-mentioned headings:

  1. Power Bi compares two columns in different tables
  2. Power Query compares two columns in different tables
  3. how to compare two tables in Power BI
  4. how to compare multiple columns in Power BI
  5. Power Bi DAX compares two columns in other tables
  6. Power Bi calculates the difference between two columns in other tables
  7. Power bi compare two columns in the same table
  8. Power bi compare two text columns in different tables

Power Bi compares two columns in different tables

Let us see how we can compare two columns in other tables in Power BI,

In this example, I am going to use the Product table data you can download it from here.

Table 1:

Power Bi compares two columns in different tables
Power Bi compares two columns in different tables

Table 2:

Power Bi compares two columns in different tables example
Power Bi compares two columns in different tables example
  • Open the Power Bi desktop, and load the table data into it.
  • Make sure both the tables have one same column so that it will make a relationship into it. Here in this example country column is the same in both the tables.
  • Add the new column and apply the below-mentioned DAX formula to it.
Compared Column = IF(RELATED('Table1'[Product])=Table2[Product],1,0)

Where,

  1. Compared Column = Calculated Column Name
  2. Table 1 and Table 2 = Tables Name
  3. Product = Column Name
  • Now in the below screenshot, you can see that the New compared columns display the result.
  • If the column rows are the same in both the tables then it displays the result as 1 else it displays the result as 0.
Compares two columns in different tables in Power Bi
Compares two columns in different tables in Power Bi

This is how to compare two columns in different tables in Power BI.

Also Read: Power BI Sum Group by

Power Query compares two columns in different tables

Let us see how we can compare two columns in other tables using Power Query in Power BI,

In this example, I am going to use the same Product table data.

Table 1:

Power Query compares two columns with different tables
Power Query compares two columns with different tables

Table 2:

Power Query compares two columns with different tables in Power BI
Power Query compares two columns with different tables in Power BI
  • Initially, load the table data into the Power BI desktop, and click on the Transform Data from the ribbon, to open the power query editor.
Compares two columns in different tables in the Power Bi
Compares two columns in different tables in the Power Bi
  • In the power query editor, simply right-click on the Query side, click on the New Query and choose Blank Query as shown below:
Power Query compares two columns in different tables
Power Query compares two columns in different tables
  • And apply the below-highlighted query, so that it will compare both the columns of the table and display the unmatched rows from the tables.
= Table.RemoveMatchingRows(Table1,Table.ToRecords(Table2))
  • In the below screenshot, you can see that it displays the unmatched rows by comparing two different tables.
Power Query compares two columns in different tables example
Power Query compares two columns in different tables example

This is how to compare two columns in other tables using Power Query in Power BI.

Check out: Power BI Report Export to PDF [With 20+ Examples]

How to compare two tables in Power BI

Let us see how to compare two tables based on the condition in Power BI,

  • Here I have used the same product table data to compare the two tables based on the condition.
  • In this example if the sold Amount value is less than 2500 and the Product is A then it should compare two tables and display the result as Show else Hidden.

Query Table:

How to compare two tables in Power BI
How to compare two tables in Power BI

Product Table 1 :

compare two tables in Power BI
compare two tables in Power BI
  • Initially, load the table data into the Power BI desktop, Make sure both the tables have one same column so that it will make a relationship into it. Here in this example country column is the same in both the tables.
compare two tables in Power BI example
compare two tables in the Power BI example
  • Add the new column and apply the below-mentioned DAX formula to it.
ShowOrHidden = 
IF (
    MAXX ( RELATEDTABLE ( 'Product Table 1' ), 'Product Table 1'[Sold Amount] ) < 2500
        && Query1[Product] = "A",
    "SHOW",
    "HIDDEN"
)

Where,

  1. ShowOrHidden = Calculated Column Name
  2. Product Table 1 & Query1 = Table Names
  3. Sold Amount & Product = Column names
  • Now in the below screenshot, you can see that the New ShowOrHidden columns display the result based on the condition applied.
  • It compares two tables and gets values, and displays the result as shown or hidden according to the condition. 
How to compare two tables in Power BI example
How to compare two tables in Power BI example

This is how to compare two tables based on the condition in Power BI.

Read: Power BI Add Calculated Column [With Various Examples]

How to compare multiple columns in Power BI

Let us see how to compare multiple columns with two different tables in Power BI,

  • Here I have used the same product table data to compare the two tables based on the condition.
  • In this example, if the sold amount value is less than 4000 and the Product is B and the country column is Mexico then it should compare two tables with multiple columns and display the result as True else False.
  • Load the table data into the Power BI desktop, Make sure both the tables have one same column so that it will make a relationship into it.
  • Here in this example country column is the same in both tables, Add the new column and apply the below-mentioned DAX formula to it.
Result = 
IF (
    MAXX ( RELATEDTABLE ( 'Product Table 1' ), 'Product Table 1'[Sold Amount] ) < 4000
        && Query1[Product] = "B" && Query1[Country] = "Mexico",
    "True",
    "False"
)

Where,

  1. Result = Calculated Column Name
  2. Product Table 1 & Query1 = Table Names
  3. Sold Amount & Product & Country= Column names
  • Now in the below screenshot, you can see that the New Result column displays the result based on the condition applied.
  • It compares two tables and gets values, and displays the result as True or False according to the condition. 
How to compare multiple columns in Power BI
How to compare multiple columns in Power BI

This is how to compare multiple columns with two different tables in Power BI.

Check: Power BI Matrix Multiple Column

Power Bi DAX compares two columns in other tables

Let us see how we can compare two columns in different tables using DAX or Measure in Power BI,

In this example, I am going to use the Product table data you can download it from here.

  • Open the Power Bi desktop, and load the table data into it. Make sure both the tables have one same column so that it will make a relationship into it.
  • Here in this example country column is the same in both the tables. Add the new column and apply the below-mentioned DAX formula to it.
Matched Column = IF(RELATED('Product Table 1'[Product])='Product Table 2'[Product],"Matches","UnMatch")

Where,

  1. Matched Column = Calculated Column Name
  2. Product Table 1 and Product Table 2 = Tables Name
  3. Product = Column Name
  • Now in the below screenshot, you can see that the New compared columns display the result.
  • If the column rows are the same in both the tables then it displays the result as Matches else it displays the result as UnMatches which are non-matching values.
Power Bi DAX compares two columns in other tables
Power Bi DAX compares two columns in other tables

This is how to compare two columns in different tables using DAX or Measure in Power BI.

Read: How to remove rows in power query editor [With various examples]

Power Bi calculates the difference between comparing two columns in other tables

Let us see how we can calculate the difference between comparing or matching two columns in different tables in Power BI.

In this example, I am going to use the Product table data you can download it from here.

  • Load the table data into it. Make sure both the tables have one same column so that it will make a relationship into it.
  • Here in this example country column is the same in both the tables. Add the new column and apply the below-mentioned DAX formula to it, which compares and finds the difference between the two columns.
Difference = IF(RELATED('Product Table 1'[Product])='Product Table 2'[Product],SUM('Product Table 1'[Sold Amount])-SUM('Product Table 2'[Sold Amount]),0)

Where,

  1. Difference = Calculated Column Name
  2. Product Table 1 and Product Table 2 = Tables Name
  3. Product & Sold Amount = Column Names
  • Now in the below screenshot, you can see that the Difference column displays the result based on the condition applied.
  • It compares two tables and gets values, and displays the result only for the matched columns rows, and for the remaining rows, it displays the value as 0 according to the condition. 
Power Bi calculates the difference between comparing two columns in other tables
Power Bi calculates the difference between comparing two columns in other tables

This is how to calculate the difference between comparing or matching two columns in different tables in Power BI.

Also check: Power BI filter between two dates [With 15+ Examples]

Power bi compare two columns in the same table

Let us see how we can compare two columns in the same table in Power BI,

In this example, I am going to use the below-mentioned sample data :

Power bi compare two columns in the same table example
Power bi compare two columns in the same table example
  • Load the table data into it. Make sure the same table has two same column data. And add the new column and apply the below-mentioned DAX formula to it.
Yes/NO = IF(LOOKUPVALUE(Sheet1[Column1],Sheet1[Column1],Sheet1[Column2])=BLANK(),"No","Yes")

Where,

  1. Yes/NO = Calculated Column Name
  2. Sheet1 = Tables Name
  3. Column1 & Column2 = Column Names
  • Now in the below screenshot, you can see that the YES/NO column displays the result based on the condition applied.
  • It compares the same tables and gets values, and displays the result only for the matched columns rows, it displays the value as Yes according to the condition. 
Power bi compare two columns in the same table
Power bi compare two columns in the same table

This is how to compare two columns in the same table in Power BI.

Read: Power BI Sum Multiple columns

Power Bi compares two text columns in different tables

Let us see how we can compare two text columns in different tables in Power BI,

In this example, I am going to use the Product table data you can download it from here. Here the Product column is of data type string which is a text column.

  • Load the table data into it. Make sure both the tables have one same column so that it will make a relationship into it.
  • Add the new column and apply the below-mentioned DAX formula to it.
Different Table = IF(RELATED('Product Table 1'[Product])='Product Table 2'[Product],"Text matches","Text Unmatches")

Where,

  1. Different Table = Calculated Column Name
  2. Product Table 1 and Product Table 2 = Tables Name
  3. Product = Column Names
  • Now in the below screenshot, you can see that the Difference column displays the result based on the condition applied.
  • It compares two tables and gets values, and displays the result only for the matched columns rows as Text Matches, and for the remaining rows, it displays the value as Text unmatches according to the condition.
Power Bi compare two text columns in different tables
Power Bi compares two text columns in different tables

This is how to compare two text columns in different tables in Power BI.

Also, you may like the below Power BI Tutorials:

In this tutorial, we have learned how to compare two columns in different tables in Power BI. Also, covered the below-mentioned headings:

  • Power Bi compares two columns in different tables
  • how to compare multiple columns in Power BI
  • Power Bi DAX compares two columns in other tables
  • Power Query compares two columns in different tables
  • Power Bi calculates the difference between two columns in other tables
  • Power bi compare two columns in the same table
  • Power bi compare two text columns in different tables
>