March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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.
Diff = SUM ( CommercialBuyIntentVisits[MetricValue] ) - SUM ( CubeData[CommercialBuyIntentVisits] )
Regards
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
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.
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..
HI @pxg08680
Have you tried the following to create a new calculated table?
TableC = UNION( EXCEPT(TableA,TableB), EXCEPT(TableB,TableA) )
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.
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]) ) )
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]) ) )
NewMeasure = IF(SUM(CubeData[CommercialBuyIntentVisits]) = SUM(CommercialBuyIntentVisits[MetricValue]), 1, 0)
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |