Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicer

Hello Guys,

 

I may need some help on Slicers in Power BI Desktop.

I want to Filter a table by year (2020, 2021 and so on). But some of the rows are assigned to multiple years, e.g. '2020; 2021' is the entry of at least one row. This yields to three possible options in the Slicer: '2020', '2021' and '2020; 2021'. 

The latter one looks pretty awkward and I want to get rid of this option, i.e. I would like the option '2020; 2021' to be contained in both filter options '2020' and '2021'.

Some ideas how to fix this?

Greetings

Need Help
  • HotChilli's avatar
    HotChilli
    5 years ago

    It sounds like you want to split the column in Power Query (using ; as the separator).

    If you choose 'Rows' from the Advanced Section, it'll do what you want.

     

    Let me know how you get on.

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    5 years ago

    It sounds like you want to split the column in Power Query (using ; as the separator).

    If you choose 'Rows' from the Advanced Section, it'll do what you want.

     

    Let me know how you get on.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    Try follow steps:

    1.create a calculated table :

    Slicer Table = DISTINCT('Table'[Year])

    2.create a new measure

    visualfilter =
VAR s_year =
    SELECTEDVALUE ( 'Slicer Table'[Year] )
VAR t_year =
    SELECTEDVALUE ( 'Table'[Year] )
RETURN
    IF ( CONTAINSSTRING ( t_year, s_year ), 1, 0 )

    3.apply this measure to visual filter pane

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.