Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Using Power BI slicer to filter table based on related table with comma-separated values

 

I'm having trouble using a slicer in Power BI to filter data in my table based on a related table. I have two tables: "Table" and "Countries". "Table" has a column called "Approved By" with comma-separated values, and "Countries" has a single column with unique country names.

I want to create a slicer based on the "Countries" table that filters the "Table" based on whether the selected country name appears in the "Approved By" column. I followed the steps to create a measure that uses a variable to get the selected country name and another variable to get the values from the "Approved By" column, then checks if the country name appears in the values using the SEARCH function. However, when I try to use this measure in a slicer, it doesn't work as expected.

Here's the measure I created:

 

 
MyFilter =
VAR SelectedCountry = MIN( Countries[Country] )
VAR ApprovedBy = MIN( 'Table'[Approved By] )
RETURN IF( SEARCH( SelectedCountry, ApprovedBy,, 0 ), 1 )

 

I added this measure to the Filters pane and set it equal to 1, but when I select a country in the slicer, it only filters the "Table" if the "Approved By" column contains that exact country name and no others. For example, if I select "United States" in the slicer, it filters the "Table" only if the "Approved By" column has the value "United States", but not if it has "United States, Canada" or "Canada, United States".

I also tried creating a bridge table with the unique values from both columns and setting up relationships between the tables, but I'm not sure if I did it correctly. How can I use a slicer based on the "Countries" table to filter the "Table" based on whether the selected country name appears in the comma-separated values in the "Approved By" column?

Need Help
Show and Tell
Tips and Tricks

1 Reply

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Icon for Resolver II rankResolver II
    3 years ago

    What I would recommend doing in this case is adding a new table using power query that contains 1 row for every combination of your unique identifier and country in your "Table" table. Then, join this new table to the original table and join your "Countries" to this new table on the country field you just created. 

     

    This can be done by referencing your "Table" in power query to create a new query/table. Then, you can use a power query function to split by-delimineter (in this case comma's). This will give you a bunch of new columns that you can then unpivot into just two. Something like this (Obviously with your unique ID as the account ID column):