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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
TomWoodward
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

PONumberSPECIFIERPRICE
1Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers100
2 200
3Architect: SAMPLE ARCHITECTS, Engineer: Sample Engineers, Owner: Sample Owner300
4Architect: SAMPLE ARCHITECTS400
5Architect: SAMPLE ARCHITECTS500

 

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

USERIDSPECIFIER
USER1Sample Engineers
USER1SAMPLE ARCHITECTS
USER3Sample Owner

 

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

PONumberSPECIFIERPRICE
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?

3 REPLIES 3
v-yueyunzh-msft
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])
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:

vyueyunzhmsft_0-1676860396597.png

 

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

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.

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

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.