Identifying a value that contains a specific value in another column

[dw700d]

Good day, I have a purchase order column with thousands of purchase orders. each purchase order has  various accounts associated with it. I would like a measure that allows me to identify any purchase order that contains Account ABC.  My objective is to be able to view all the accounts affiliated with any purchase order that has an account ABC.  For example the measure would return "Purchase No" 1 because it contains ABC but it would also show me Account WES as well.

Purchase No	Account
1	ABC
1	WES
2	ABC
2	DFG
3	LOM
3	NED
4	ABC
4	YUU
5	LOE
5	NSA
6	ERR
6	FRR

[Anonymous · Accepted solution]

Hi @dw700d ,

Please have a try.

Create a measure.

Measure = 
var connect = CONCATENATEX(FILTER(ALL('Table'),'Table'[Purchase No]=SELECTEDVALUE('Table'[Purchase No])),'Table'[Account]&" ")
VAR RESULT = IF(SELECTEDVALUE('Table'[Account])="ABC",connect,BLANK())
return RESULT

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @dw700d ,

Is the above table you are expecting as a output.

Please let me know so that I can share the way I have achieved this.

[Ashish_Mathur]

Hi,

What end result are you expecting?  Please show that clearly.

[parry2k]

@dw700d recently solved similar request, check this post. Solved: Re: Filter a table by Slicer value and return all ... - Microsoft Power BI Community and it also has a pbix attachment, you can tweak it as you see fit.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[VijayP]

@dw700d 

I think there are ways . 
1. you can use the filter pane and in the visual filter select only abc from Account Field which shows only POs pertains to ABC.
2. Also you can create a Slicer of all accounts and select ABC to show only Purchase orders of ABC.
If you have already know about it, please let what is should be the final outcome as snapshot to explain better