Create advanced Slicer

[Anonymous]

Currently I have a variable 'Location' that looks like this when I put it in a slicer: 

Location

-Canada; France

-France

-Italy

-Mexico; France; China

-China

-Canada; Mexico

(+ 15 other options)

However, I would like that the slicer would just show individual countries, but when I select one of them it is actually selecting all the options that include that country. 

The slicer would look like this:

Location

-Canada

-France

-Italy

-Mexico

-China

But when I select e.g. France, it is actually selecting the options 'Canada, France' 'France' and 'Mexico; France; China'

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous,

Current you can't directly use the default slicer feature to achieve your requirement.

I'd like to suggest you create a new table to expand these concatenate location values to single location list.

New Table =
VAR _path =
    SUBSTITUTE ( VALUES ( 'Table'[Column] ), ";", "|" )
VAR _length =
    PATHLENGTH ( _path )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Location", PATHITEM ( _path, [Value] )
    )
RETURN
    pathtable

Then you can use new table fields as source of slicer and write a measure formula to extract the current location to lookup table records and return tag. 

SEARCH function (DAX) - DAX | Microsoft Learn

Applying a measure filter in Power BI - SQLBI

After these steps, you can use this formula on the 'visual level filter' to filter table records.

Regards,

Xiaoxin Sheng