Forum Discussion

TomWoodward's avatar
TomWoodward
Frequent Visitor
3 years ago

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 n...
Tips and Tricks
Tutorial Requests
v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

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's avatar
TomWoodward
Frequent Visitor
3 years ago

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's avatar
    TomWoodward
    Frequent Visitor
    3 years ago

    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