Forum Discussion

Saimen's avatar
Saimen
Frequent Visitor
6 years ago
Solved

Create filter with unique values based on multivalue cells

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!

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      Saimen
      Frequent Visitor
      5 years ago

      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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.