Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.