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

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]) )
          )
    • pxg08680's avatar
      pxg08680
      Icon for Resolver III rankResolver III
      9 years ago

       

      I did'nt get what this means. 

       

      Thanks

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

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