Skip to main content
cancel
Showing results for 
Search instead 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

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

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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