Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

Slicer to filter based on multiple values

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

1Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers100
2 200
3Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers, Owner: Sample Owner300


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

USER1Sample Engineers
USER3Sample Owner


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

1Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers100
3Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers, Owner: Sample Owner300



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?

Community Support
Community Support

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])
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


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

var _select_spec = VALUES(SPECIFIERS[Customer Name])
var _cur_spec = MAX('PO Listing 2009-Present'[Specifiers])
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.

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

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.