Slicer with custom dropdown

[Andresan]

Hello,

I am looking to have a slicer dropdown with custom option values for an Aging visual. I have a column that already contains the days opened and I need to have the dropdown that shows:

0-30
31-45
46-60
61-90
90+

and filter based on those selections. 

[Andresan]

Hey guys! @MFelix & @parry2k Thanks for jumping on this so quickly.

My goal is to use the slicer on the page level. I would like for it to filter other visuals as well.

My Days open column is as follows:

Days Open

[parry2k]

@Andresan not sure what you just pasted.

[Andresan]

@parry2k  That was the column pasted as a table format. I just changed it to a screenshot of the column. 

Here is the table format:

Days Open
3
17
17
17
17
17
17
17
14
13
12
7
5
4
79
7
7
6
2
29
29
29
29
29
29
29
15
29
29
4
0
29
29
29
29

Screenshot: 

This is how I'd like the dropdown to look/work:

[parry2k]

@Andresan see attached pbix files. It will show data based on the selection in the slicer, if no selection is made then it will show everything.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Andresan]

@parry2k, Thanks for your help. What is it that makes the dropdown filter the table? I am having trouble getting it to work on my line chart visual. I am trying to understand the requirements that make it work. 

I tried to replicate everything you created to my example but couldn't get it to work.  My goal was to get to an aging visual report that displays the average open days by month. 

It's also filtering only the visuals that contain the measure and not the whole page. 

[v-deddai1-msft · Accepted solution]

Hi @Andresan ,

Based on the Mfelix's pbix file, If you reaaly want to apply the filter on the page-level filter, I suggest you use the calculated column for the slicer:

Column =

SWITCH (

    TRUE (),

    Sales[Days] >= 0

        && Sales[Days] <= 30, "0-30",

    Sales[Days] >= 31

        && Sales[Days] <= 45, "31-45",

    Sales[Days] >= 46

        && Sales[Days] <= 65, "46-60",

    Sales[Days] >= 61

        && Sales[Days] <= 90, "61-90",

    "90+"

)

Best Regards,

Dedmon Dai

[MFelix]

Hi @Andresan ,

Believe you need to create a table with the following format:

ID	Range	Min	Max
1	0-30	0	30
2	31-45	31	45
3	46-60	46	60
4	61-90	61	90
5	+90	90	100000

Now you need to add a measure similar to this:

FIltering By days = IF(selectedvalue(Table[Days]) <= Max(Table[Max]) &&selectedvalue(Table[Days]) >= MIN(Table[MIN]); 1 ; 0)

Now just filter your visualization or report by the value 1 and use the range column as your slicer.

[parry2k]

@MFelix this will work but only my concern is that every visual where this data needs to be filtered, one has to use the visual level filter. Again, no doubt it will work, I would rather have it in measure so that measure takes cares of it. Nothing right or wrong here, just my thought process. Cheers!!

[MFelix]

Hi @parry2k ,

Once again aswering at the same time. 😄

Completly agree with you, no doubt but without any background information just wanted to make @Andresan  think about one possible route.

This can also be used at page level filtering depending on the visualizations but again little information.

[parry2k]

@Andresan how your raw data look like? Paste sample data in a table format and expected output.