Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create advanced Slicer

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!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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