Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |