Create filter with unique values based on multivalue cells

[Saimen]

Hello,

I've got article information in my model. with a field that contains what sport the article is. It can be just 'voetbal' (soccer) but it can also be multiple values like voetbal,casual (soccer, casual):

This data is also stored this way in the SQL. 

My question is: how can i create an slider that has only the unique values in this list and when i (for example) select 'Fitniss' it selects all the fitness articles, even when its an multivalue field that contains fitness.

Thanks a lot!

[Ashish_Mathur]

Hi,

Ideally, using the Query Editor, you should have each Sporting activity should be in its own cell.  This can be accomplished by using Split Column > By rows.  You may specify the delimiter as comma.

Hope this helps.

[v-alq-msft · Accepted solution]

Hi, @Saimen 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test:

You may create a measure as below.

Visual Control = 
IF(
    COUNTROWS(
        FILTER(
            DISTINCT(Test[Key Word]),
            CONTAINSSTRINGEXACT(SELECTEDVALUE('Table'[Article information]),[Key Word])
        )
    )>0,
    1,0
)

Finally you need to put the measure in the corresponding visual level filter to display the result.

Best Regards

Allan

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

[Saimen]

Hi all guys who answered me,

I just came back from holiday so sorry for the late response. I tried the solution of @v-alq-msft and that works out just fine!
Thanks for that and the other guys for helping me out here.

Just got one question left about this.
When i filter an sport, it filters my article correctly in a table, but it doesn't seem to work with an graph.

I just made an graph of the brand name + turnover and i've putted also the Sportfilter = 1 als an filter in the diagram.
For an table that worked (see image) but for graph i got nothing.

Al relations are there and when i check with other filter options (no sport) and delete the sportenfilter in the graph, the graph works normal.

Thanks again!

[amitchandak]

@Saimen , create a slicer on an independent table or use cross filter to remove join and then use a measure like

calculate(count(Table[name), filter(Table, CONTAINSSTRING(Table[article ], selectedvalue(Category[Category]))))