Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need something like lookup or DAX

Hi,

I have two columns, ColumnA and ColumnB having the below records

 

Actual Data

Column 1Column 2
DanDan
RogerJeff
EugilineMax
SamuelRoger
PeterEugiline

 

I want to compare Above two columns and display result as shown below in powerBi using formula

Result
Jeff
Max

 

Result
Samuel
Peter

 

Thanks,

Ravi

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    Please create calculated columns using the following formulas and get expected result shown in screenshot.

    Column3 = IF(Test[Column 1]=Test[Column 2],BLANK(),Test[Column 1])

Column4 = IF(Test[Column 1]=Test[Column 2],BLANK(),Test[Column 2])

     

    Best Regards,
    Angelia

7 Replies

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

    Hi Anonymous,

    >>I want to compare Above two columns and display result as shown below in powerBi using formula

    How to compare the two columns? I am unable to reproduce based on your given sample data. Maybe some values are missing out in your given example.


     I post the solution based on my understanding for your requriement.

    Type the example data table to PowerBI.



    Please click Nwer Table under Modeling on home page. type the following formula, you will get the two expected table.

     

    Tab1 = SELECTCOLUMNS(FILTER('Actual Table',OR('Actual Table'[Column 2]="Jeff",'Actual Table'[Column 2]="Max")),"Result",'Actual Table'[Column 2])



    Tab2 = SELECTCOLUMNS(FILTER('Actual Table',OR('Actual Table'[Column 1]="Samuel",'Actual Table'[Column 1]="Peter")),"Result",'Actual Table'[Column 1])

     




    If this is not what you want, please share more details for further analysis.

    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Angelia,

      I am looking for more of dynamic approach, I have two columns as below, First I have to compare records of column1 with column2 and display the non-matching records(samuel ,peter) in a new column(say column3), similarly comapre records of column2 with column1 and display the non-matching records(Jeff,max) in another column(say column4).

       

      Actual

      Column 1

      		Column 2
      DanDan
      RogerRoger
      EugilineEugiline
      SamuelJeff
      PeterMax

       

      Thanks,

      Ravi

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

        Hi Anonymous,

        Please create calculated columns using the following formulas and get expected result shown in screenshot.

        Column3 = IF(Test[Column 1]=Test[Column 2],BLANK(),Test[Column 1])

Column4 = IF(Test[Column 1]=Test[Column 2],BLANK(),Test[Column 2])

         

        Best Regards,
        Angelia