Searching value in one column in another column

[Anonymous]

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

[MatejZukovic · Accepted solution]

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]

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

[Anonymous]

Thanks A lot.

That is what i was looking for.