cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Resolver III

compare columns in two tables with same column names

Hi Everyone,

I have two tables A&B and have a column which is same in both the tables. I would like to compare the two columns and see if there is any difference between them and make a visual for that difference. both the tables are related using the data field.

For example, Column1 in TableA and Column1 in TableB are same and for getting the difference i tried tha dax query Diff= TableA[column1] - TableB[column1] and i have been getting errors and even if it executes I am getting incorrect values.

Can you help me with this...?

The solution is in real urgency....

Thank You

1 ACCEPTED SOLUTION
Employee

Hi @pxg08680,

According to your description above, you should be able to use the formula below to create a new measure and show it on the report to get the actual difference.

```Diff =

Regards

11 REPLIES 11
Employee

Hi @pxg08680,

both the tables are related using the data field.

For example, Column1 in TableA and Column1 in TableB are same and for getting the difference i tried tha dax query Diff= TableA[column1] - TableB[column1] and i have been getting errors and even if it executes I am getting incorrect values.

What's the relationship between TableA and TableB(One to One, or One to Many, or Many to One)?

If there is a (One to One, Many to One) relationship between TableA and TableB, then you should be able to use the formula below to create a new calculate column in TableA to calculate the difference.

```Diff = TableA[column1] - RELATED ( TableB[column1] )
```

Regards

Resolver III

I have used this dax query to see if there is any difference between two data columns and represeted as 1 or 0.

Now I want to find the acutal difference between the values.

How do i do that..

Thank You

Employee

Hi @pxg08680,

According to your description above, you should be able to use the formula below to create a new measure and show it on the report to get the actual difference.

```Diff =

Regards

Resolver III

Hey Thank You for the help.

I actually did it in a different way and it worked.

Resolver III

Hi , Thank You for the reply.

I have tried this a couple of times and I dont see the data is matching. I was getting different values than the expected ones.

My data is related on many-one relationship. I have related my data on a DATE column. For more info one of my table has 9 columns and other table has 15 columns. Does that matter..

Employee

Have you tried the following to create a new calculated table?

```TableC = UNION(
EXCEPT(TableA,TableB),
EXCEPT(TableB,TableA)
)```

Proud to be a Datanaut!

Resolver III

I have tried it but the problem is I dont have same number of columns in both the tables as EXCEPT takes only same number of column tables. As you can see the below picture i was trying to compare data. Pink color data is from a differemce source and ornage color is from a different source.

Employee

A slight tweak is to use the SELECTCOLUMNS function to choose which columns you want from each table (and to make sure the column count alignts)

```TableC =
UNION(
EXCEPT(SELECTCOLUMNS(TableA,"Col",[Column1]) ,SELECTCOLUMNS(TableB,"Col",[Column1]) ),
EXCEPT(SELECTCOLUMNS(TableB,"Col",[Column1]) ,SELECTCOLUMNS(TableA,"Col",[Column1]) )
)```

Proud to be a Datanaut!

Resolver III

I did'nt get what this means.

Thanks

Employee

Hi there

Please try this.  Does your Model table have a column called [CommercialBuyIntentVisits] which is also the same name that you have one of the tables?

```TableC =
UNION(
EXCEPT(
),
EXCEPT(
)
)```

Proud to be a Datanaut!

Resolver III

I have used this dax query to find if there is any difference between the columns and represented it as 1 or 0, whic say yea or no. How do I get the difference in value like on 4/5/2017 I have a difference of 2.

Thank You

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors