Forum Discussion
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 | MJ2 | JK2 |
| 111 | MJ2 | MJ2 |
| 111 | MJ2 | SK4 |
| 222 | HJ1 | MJ2 |
| 222 | HJ1 | SK4 |
| 222 | HJ1 | UH6 |
| 222 | HJ1 | HJ1 |
I want my results to look like:
| Requisition ID | Assigned ID | Approver ID | Audit |
| 111 | MJ2 | MJ2 | Found |
| 222 | HJ1 | Not Found |
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
5 Replies
- MatejZukovic
Resolver I
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
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 ID Assigned ID Approver ID Audit 111 MJ2 MJ2 Found 222 HJ1 Not Found - MatejZukovic
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.
Thanks I was looking for similar thing to look Column1 value (A) in Column2 values (A,B) in same table.
Solution: CONTAINSSTRING worked
