cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pxg08680
Resolver III
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

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

 

Diff =
SUM ( CommercialBuyIntentVisits[MetricValue] )
    - SUM ( CubeData[CommercialBuyIntentVisits] )

 

Regards

View solution in original post

11 REPLIES 11
v-ljerr-msft
Microsoft
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

Capture.PNG

NewMeasure = IF(SUM(CubeData[CommercialBuyIntentVisits]) = SUM(CommercialBuyIntentVisits[MetricValue]), 1, 0)

 

 

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

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

 

Diff =
SUM ( CommercialBuyIntentVisits[MetricValue] )
    - SUM ( CubeData[CommercialBuyIntentVisits] )

 

Regards

Hey Thank You for the help. 

 

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

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

 

Phil_Seamark
Microsoft
Microsoft

HI @pxg08680

 

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

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the reply.

 

 

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. 

 

Untitled.png

 

 

Hi @pxg08680

 

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]) )
          )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Capture.PNG

 

I did'nt get what this means. 

 

Thanks

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(
                SELECTCOLUMNS(CommercialBuyIntentVisits,"Col",[MetricValue]) ,
                SELECTCOLUMNS('Model',"Col",[CommercialBuyIntentVisits]) 
                ),
         EXCEPT(
             SELECTCOLUMNS('Model',"Col",[CommercialBuyIntentVisits]) ,
             SELECTCOLUMNS(CommercialBuyIntentVisits,"Col",[MetricValue]) 
             )
       )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

NewMeasure = IF(SUM(CubeData[CommercialBuyIntentVisits]) = SUM(CommercialBuyIntentVisits[MetricValue]), 1, 0) Capture.PNG

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Dev Camp Session 30

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

2022 Monthly Feature Releases

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