Forum Discussion

BalazsNy's avatar
BalazsNy
Icon for Helper I rankHelper I
1 year ago
Solved

Filtering a column by single value which contains multiple options

Hi All,


I have a report with 2 tables:

  • The 1st collects Feedbacks of employees within company (imported from a SP list).
    These feedbacks can have single or multiple categories (in case of multiple selection, categories are separated by comma within the cell).
  • The 2nd is a Category table which contains a single column with each category options individually. I would like to apply a filter based on the values of this column.

In practice, I would like to have the option to filter feedbacks selecting categories individually, which would filter out all the feedbacks that contains the selected category, like this:



Thanks for any tips in advance.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi BalazsNy ,

    Thanks for MFelix's reply!
    And BalazsNy , you can try this way:
    Use this DAX to create a measure:

    Filter = 
VAR SelectedWords = VALUES('Category'[Submission category])
RETURN
SUMX(
    'Feedbacks',
    IF (
        COUNTROWS(
            FILTER(
                SelectedWords,
                CONTAINSSTRING([category], [Submission category])
            )
        ) > 0,
        1,
        0
    )
)

    Then set it up as shown below:

    And the final output is as below:


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

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 year ago

    Hi BalazsNy ,

     

    Add the following measure to you model:

     

    Filter by category = var searchword = SELECTEDVALUE(Category[Submission category])
var tables =   ADDCOLUMNS (
        VALUES ( Feddback[Category]),
        "CategorySearch", SEARCH (searchword, Feddback[Category], 1, BLANK () )
    )
Return
SUMX(tables, [CategorySearch])

     

     

    Now use this measure to filter the visusalization with the value is not blank:

     

    Be aware that since you refer that the filtering will be selecting categories individually I assume you only want a category at a time if the user does multi select this will break the measure.

    • BalazsNy's avatar
      BalazsNy
      Icon for Helper I rankHelper I
      1 year ago

      Thank you MFelix , basically the category filter works on it's own, but if any other filter is applied (e.g. date filter), that also ruins this measure, and category is not filtered anyomre...

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi BalazsNy ,

        Thanks for MFelix's reply!
        And BalazsNy , you can try this way:
        Use this DAX to create a measure:

        Filter = 
VAR SelectedWords = VALUES('Category'[Submission category])
RETURN
SUMX(
    'Feedbacks',
    IF (
        COUNTROWS(
            FILTER(
                SelectedWords,
                CONTAINSSTRING([category], [Submission category])
            )
        ) > 0,
        1,
        0
    )
)

        Then set it up as shown below:

        And the final output is as below:


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