Slicer to filter based on multiple values

[TomWoodward]

I have a table of orders. One of the columns within this table is "Specifiers". This column can contain up to three different specifiers involved in a job. or it can be blank, all three options are not always shown either. I also have a table relating my users to each specifier. I have a report listing all of the orders and need to have a slicer where I can select a UserID and filter the orders to only orders they were involved in.

Example of my orders table columns

PONumber	SPECIFIER	PRICE
1	Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers	100
2		200
3	Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers, Owner: Sample Owner	300
4	Architect: SAMPLE ARCHITECTS	400
5	Architect: SAMPLE ARCHITECTS	500

Example of my table relating my users to each specifier (each specifier has one of my users assigned to it)

USERID	SPECIFIER
USER1	Sample Engineers
USER1	SAMPLE ARCHITECTS
USER3	Sample Owner

So if I were to use the slicer to select USER1, I should only see the below.

PONumber	SPECIFIER	PRICE
1	Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers	100
3	Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers, Owner: Sample Owner	300

I have tried to split out the specifiers into individual columns, but cannot relate three columns in the orders table to the SPECIFIER column in the users table. I also don't want to split it out into unique columns, as if USER1 calls on both the architect and engineer on the job, it would be double counted in my report totals.

Is there a way do relate one column to others using AND/OR style of relationship, or possibly some way to relate the users table to the un-delimited specifier column but count it as related if the SPEICIFERS cell in the orders column CONTAINS a match instead of EQUALS exactly?

[v-yueyunzh-msft]

Hi , @TomWoodward 

According to your description, you want to "Slicer to filter based on multiple values" and you do not want to split the columns.

If this , i think you do not make any relationship between tables.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can click "New Measure" and enter this:

Measure = var _select_spe = VALUES('User Table'[SPECIFIER])
var _cur_spe = MAX('Oerder Table'[SPECIFIER])
return
IF(COUNTROWS( FILTER( _select_spe , SEARCH([SPECIFIER] , _cur_spe,,-1)>0))=COUNTROWS(_select_spe) ,1, -1)

(3)Then we can put the measure on the "Filter on this visual" and configure it , then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[TomWoodward]

This did not work for me. I get -1 in the measure column no matter what I have selected in my USERID Slicer. So I get no results in my visual table at all. Within the search function on the last line, your search for parameter is [SPECIFIERS]. Is this the SPECIFIERS column you have in the order table? or in the USER Table? Mine forces me to use the one within the user table.

My User table is called "Specifiers", the order table is the "PO Listing - 2009-Present"

This is what I have my measure in as

SPECIFIER SELECT MEASURE = 
var _select_spec = VALUES(SPECIFIERS[Customer Name])
var _cur_spec = MAX('PO Listing 2009-Present'[Specifiers])
return
IF(COUNTROWS( FILTER( _select_spec, SEARCH([Customer Name], _cur_spec, , -1)>0))=COUNTROWS(_select_spec) ,1, -1)

Then I have a slicer on that page that is just the SPECIFIERS[USERID] column.

[TomWoodward]

Also, in your code, when I select User1, who is assigned to both SAMPLE ARCHITECTS and Sample Engineers, I should see your PO 1, 3, 4, and 5 all show up. Your example only returns a 1 in this column when both SAMPLE ARCHITECTS and Sample Engineers are in the column, I need it to be and/or so that I can select USER1 and see everything any of his engineers or architects were involved in