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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.