Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Searching value in one column in another column

Hi Experts,   I need to search for a value of column "Assigned ID" in "Approver ID" respective of their "Requisition ID" eg   Requisition ID Assigned ID Approver ID 111 MJ2 MK1 111 ...
Need Help
Tips and Tricks
  • MatejZukovic's avatar
    MatejZukovic
    7 years ago

    Hi Anonymous ,

     

    you may consider creating 2 columns :

    1. Flag comparing Assigned ID & Approver ID. 

    Audit_Flag = 'Table'[Assigned ID] = 'Table'[Approver ID]

    2. All Requisitions IDs which contain at least 1 TRUE flag from previous column will be marked as TRUE otherwise FALSE

    Audit_Result = CALCULATE(CONTAINS('Table','Table'[Audit_Flag],TRUE()),ALLEXCEPT('Table', 'Table'[Requisition ID]))

    Final table will look like this:

    Let us know if it helped!

    Matej

     

     

MatejZukovic's avatar
MatejZukovic
Icon for Resolver I rankResolver I
7 years ago

Hi Anonymous ,

 

you may consider creating 2 columns :

1. Flag comparing Assigned ID & Approver ID. 

Audit_Flag = 'Table'[Assigned ID] = 'Table'[Approver ID]

2. All Requisitions IDs which contain at least 1 TRUE flag from previous column will be marked as TRUE otherwise FALSE

Audit_Result = CALCULATE(CONTAINS('Table','Table'[Audit_Flag],TRUE()),ALLEXCEPT('Table', 'Table'[Requisition ID]))

Final table will look like this:

Let us know if it helped!

Matej

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks A lot.

     

    That is what i was looking for.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    MatejZukovic 

    Thanks for the solution and the final table loooked great. I require one more help on this, I need a table view on dashboard with one line each of requisition ID with its audit column. Just like the below table:

    Requisition IDAssigned IDApprover IDAudit
    111MJ2MJ2Found
    222HJ1 Not Found
    • MatejZukovic's avatar
      MatejZukovic
      Icon for Resolver I rankResolver I
      7 years ago

      Hi Anonymous ,

       

      you can create one more column :

       

      Then create a table in your report bringing in Requisition ID, Assigned To, and the column created above. Unselect "Other Matches" in the table through a filter.

       

       

  • ramdasanudas's avatar
    ramdasanudas
    Frequent Visitor
    1 year ago

    Thanks I was looking for similar thing to look Column1 value (A) in Column2 values (A,B) in same table.

    Solution: CONTAINSSTRING worked