cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhanes22
Helper I
Helper I

Slicer with distinct values from a column that has multiple values?

Hey All,

 

I'm very much a novice when it comes to PowerBI, so please be gentle here. I have an issue where I have a column named specifications in my item master table that has multiple different specs for each item ID,

 

example: item ID - "2778-FR" has specs: FDA Approved, RGD, GFLT, etc. 

                item ID - "3489-GT" has specs: UP, Dupont Viton, Class VI, FDA Approved, etc. 

 

What I want to do is have a slicer dropdown that picks out the distinct values from this spec column and then what ever is picked from this slicer will filter the data table below it with the corresponding items that have those specifications. 

 

bhanes22_1-1622812063565.png

 

 

1 ACCEPTED SOLUTION

Hi @bhanes22 

My previous measure only deals with a single select situation. For multiple specs selections, you could use below measure and set show items when the value is greater than 0.

Flag 2 = 
CALCULATE (
    COUNTROWS ( 'master table' ),
    FILTER (
        'master table',
        SUMX (
            specifications,
            SEARCH ( specifications[spec], 'master table'[specifications], 1, 0 )
        ) > 0
    )
)

 

I found above measure from below blog, you could refer to it for more ideas.

SEARCH FUNCTION WITH A LIST VALUE - Microsoft Power BI Community

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @bhanes22 

 

You need to have all distinct spec values in a column of an independent table and use this column in a slicer. Then create below measure and drag it into the table visual's filter pane and set value is 1.

Flag = 
VAR selectedSpec = SELECTEDVALUE(specifications[spec])
VAR Specs = SELECTEDVALUE('master table'[specifications])
RETURN
IF(CONTAINSSTRING(Specs,selectedSpec),1,0)

060801.jpg

Download the attachment for details.

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

This worked perfectly! Thank you so much for your help! One quick question and its not the end of the world if this is not possible but it doesn't seem to filter properly on multiple spec selections, would that be possible to filter on multiple specs? 

Hi @bhanes22 

My previous measure only deals with a single select situation. For multiple specs selections, you could use below measure and set show items when the value is greater than 0.

Flag 2 = 
CALCULATE (
    COUNTROWS ( 'master table' ),
    FILTER (
        'master table',
        SUMX (
            specifications,
            SEARCH ( specifications[spec], 'master table'[specifications], 1, 0 )
        ) > 0
    )
)

 

I found above measure from below blog, you could refer to it for more ideas.

SEARCH FUNCTION WITH A LIST VALUE - Microsoft Power BI Community

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!