Need help figuring out how to get a visual to display what I want it to display

[jgiles]

Hi,

I have a slicer on a page that contains company names, along with a table visual that shows the contact for the company.  Then I want another table visual that displays anyone within the contact's agency that is a member of a certain board.  Right now my board members visual only shows the contact - which it shouldn't because they aren't a board member.  I'm still learning and I'm not sure if I need to do measures or what approach I should take.

Image of what it looks like with dummy data:

Image of what I'd like it to look like with dummy data:

The Agency Board Members visual - lists the two people from the contact's agency that are members of the board.

Dummy Tables
Account Table

Company	AccountID
ABC Company	1234
123 Inc	5678

Contact Table

AccountID	Name	Board	AgencyID
1234	Jane Smith		1111
5555	Walt Jones		1111
999	Marc Samuels	Direct Board	2222
8787	JD Banks		2222
6541	Bay Martin	Indirect Board	3333
8899	Ivy Jester	Indirect Board	3333
5678	Marlon Blank		3333

Agency Table

AgencyID	AgencyName
1111	Sesame Street Agency
2222	Muppet Inc
3333	Frank Agency Inc

Thank you,

Julie

[deevaker]

refer to the attached

pbix 

[jgiles]

@lbendlin Thank you!  I appreciate you taking the time to help me.  That works - unfortnuately, I missed putting a table in the original help question.  I've been trying to modify your example to work but I'm not having any luck.  Any suggestions on how I can modify it for the table I forgot?

Companies Table
CompanyAccountID

ABC Company	1234
123 Inc	5678
Blue Company	1111
Red Company	2222

PolTbl
AccountIDBrokerID

1234	111
7777	222
1111	333
2222	444
3333	555
4444	666
5678	777

Contact Table
ContactIDNameBoardAgencyID

111	Jane Smith		1111
222	Walt Jones		1111
333	Marc Samuels	Direct Board	2222
444	JD Banks		2222
555	Bay Martin	Indirect Board	3333
666	Ivy Jester	Indirect Board	3333
777	Marlon Blank		3333

Agency Table

1111	Sesame Street Agency
2222	Muppet Inc
3333	Frank Agency Inc

[lbendlin · Accepted solution]

see attached

[jgiles]

@lbendlin Thank you very much for your help!  Appreciate it.

[lbendlin]

BoardVisible := 
var c = values(Companies[AccountID])
var a = CALCULATETABLE(values(Contacts[AccountID]),ALLEXCEPT(Contacts,Contacts[AgencyID]))
return if (countrows(intersect(a,c))>0,1,0)

see attached.