cancel
Showing results for
Did you mean:
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
Microsoft

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
Microsoft

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

Microsoft

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

Microsoft

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.

Microsoft

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

Microsoft

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

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors