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 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 

  • 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

11 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    HI pxg08680

     

    Have you tried the following to create a new calculated table?

     

    TableC = UNION(
                EXCEPT(TableA,TableB),
                EXCEPT(TableB,TableA)
                )
    • pxg08680's avatar
      pxg08680
      Icon for Resolver III rankResolver III
      9 years ago

      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. 

       

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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]) )
          )
  • 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

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

       

    • 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