The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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 I was looking for similar thing to look Column1 value (A) in Column2 values (A,B) in same table.
Solution: CONTAINSSTRING worked
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 |
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 A lot.
That is what i was looking for.