Forum Discussion

pxg08680's avatar
pxg08680
Icon for Resolver III rankResolver III
9 years ago
Solved

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...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    Diff =
SUM ( CommercialBuyIntentVisits[MetricValue] )
    - SUM ( CubeData[CommercialBuyIntentVisits] )

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

pxg08680's avatar
pxg08680
Icon for Resolver III rankResolver III
9 years ago

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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. :smileyhappy:

     

    Diff =
SUM ( CommercialBuyIntentVisits[MetricValue] )
    - SUM ( CubeData[CommercialBuyIntentVisits] )

     

    Regards

    • pxg08680's avatar
      pxg08680
      Icon for Resolver III rankResolver III
      9 years ago

      Hey Thank You for the help. 

       

      I actually did it in a different way and it worked.