Forum Discussion
Post PatronIdentifying a value that contains a specific value in another column
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 |
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 RESULTBest 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.
5 Replies
- VijayP
Community Champion
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 - parry2k
Super User
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.⚡
- Ashish_Mathur
Hi,
What end result are you expecting? Please show that clearly.
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.
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 RESULTBest 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.
