DAX Help with returning rows

[Anonymous]

Hi All,

I have the below sample data:

EE ID	Name	State
1111	Santa	NH
1111	Santa	MA
1111	Santa	CA
23	Frost	CA
23	Frost	NH
123123	Fairy	OH
123123	Fairy	ID

I have a drop down slicer in my workbook using the "State" field. The records above show what states people lived in. What I want to do is be able to click specifically on the state "NH" in the drop down slicer and have it also return the records where they previously lived because I want to use this in a graph visual, as well as a matrix visual for various records to see where they lived for facilities planning.

So for example, if I click on NH, it should just return these records (Santa and Frost):

EE ID	Name	State
1111	Santa	NH
1111	Santa	MA
1111	Santa	CA
23	Frost	CA
23	Frost	NH

If I did not click on NH it would show all records in the table (as if I had just SELECT ALL checked) or the state that I selected otherwise (Ex. CA). 

Many thanks!

[Greg_Deckler · Accepted solution]

I created an Inverse Selector for this type of thing which might help in your case:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290

[Greg_Deckler · Accepted solution]

I created an Inverse Selector for this type of thing which might help in your case:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290

[Anonymous]

@Greg_Deckler Thanks, Greg. I'll try this out. 

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Greg_Deckler's solution could work, you could refer to below steps:

Create below new table:

New Table = SELECTCOLUMNS('Table1',"Name",[Name],"State",[State])

Create below measure:

Standard Selector = 
VAR __dept = MAX([Name])
VAR __products = VALUES('Table1'[State])
VAR __table = SELECTCOLUMNS(FILTER(ALL('New Table'),[State] IN __products),"__dept",[Name])
RETURN
IF(__dept IN __table,1,BLANK())

Use 'New Taable'[State] as slicer and set slicer for the measure:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

@v-danhe-msft This worked. thank you!