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:

Table 2:

• 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.

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:

Table 2:

• 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.

• In the power query editor, simply right-click on the Query side, click on the New Query and choose Blank Query as shown below:

• 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.

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:

Product Table 1 :

• 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.

• 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. 

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. 

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.

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. 

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 :

• 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. 

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.

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

Also, you may like the below Power BI Tutorials:

• How to merge columns in Power Query
• How to Merge Column in Power BI
• Power BI Slicer Multiple Columns
• Power BI Create Table From Another Table
• Power BI divides two columns [With Examples]
• Power BI Bookmarks [With 21 Examples]
• Power bi change color based on value [With 13 real examples]
• Power BI sync slicers [With 15 useful examples]
• Power BI calculated column [With 71 Useful Examples]
• How to add column with a fixed value in Power BI
• How to Merge Tables in Power BI

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