Show rows from a table after matching one value against that row

[pb1051]

Okay, I can't seem to find any examples of what I'm trying to achieve online in the community or on Youtube. 

I have a single table:

Project	Vendor
1234	John
1234	Smith
1234	Jane
4321	Smith
4321	Jane
456	John
456	Smith

I have a slicer for Vendor and I would like to use it to see a table with all the Vendors that match the related Project/Projects with that selected Vendor (John in this case):

Project	Vendor
1234	John
1234	Smith
1234	Jane
456	John
456	Smith

How can I achieve this? I figured there had to be a way to search the rows for that selected vendor and return all matching project rows for not only them but for all that match that project code.

Any help would be appreciated!

[Greg_Deckler · Accepted solution]

@pb1051 I would create a disconnected table like this: DISTINCT('Table'[Vendor]). In other words, not related to your current table. Then use a complex selector: The Complex Selector - Microsoft Power BI Community

In your case, something like:

Selector = 
  VAR __Vendor = MAX('Vendor table'[Vendor])
  VAR __Projects = SELECTCOLUMNS(FILTER('Table',[Vendor] = __Vendor),"__Project",[Project])
  VAR __Project = MAX('Table'[Project])
RETURN
  IF(__Project IN __Projects, 1, 0)

Put this measure in your visual filter and filter for 1

[pb1051]

Thanks, @Greg_Deckler .

Should I create the disconnected table with both columns in this case or with only [Vendor]? I tried creating it with one column and the result I get when I add the measure as a filter is I get the last alphabetical ordered vendor when no slicer has been selected.

[Greg_Deckler]

@pb1051 Right, that is because of the MAX. You could use SELECTEDVALUE instead, that will return BLANK by default if more than one Vendor is in context. You could use an addition IF to check for ISBLANK and just return BLANK or something in that instance.