topic Re: How to set default value for slicer but enabling multiple slicer selection in DAX Commands and Tips

How to set default value for slicer but enabling multiple slicer selection

arielcedola — Wed, 15 Jul 2020 14:46:04 GMT

Hi,

I'm using RANKX to set a default value to a filter in several visuals, as follows:

YearRank = RANKX(ALLSELECTED(Sales[Year]), CALCULATE(MIN(Sales[Year])),,DESC,Dense)

I set the current year (top ranked year in the ranking) as the default year in each visual by applying the Top N filter type as follows:

In this way I see only the records for the current year when I come to the report, which is the effect I was looking for. Therefore with the slicer I can easily see the records for the previous years, but if I want to see records for 2 or 3 years together, through a multiple selection in the slicer, obviously using this method it's not possible.

How could I set the by default visualization of the records for the current year, but having still the chance to select multiple years to visualize?

Here the link to the pbix file.

https://drive.google.com/file/d/1Z3JizbdKVR4XV98K0zdFopTCAWULumdH/view?usp=sharing

Thanks.

Re: How to set default value for slicer but enabling multiple slicer selection

lbendlin — Wed, 15 Jul 2020 14:59:06 GMT

Allow your report users to modify filters, and teach them how to change that particular one.

Re: How to set default value for slicer but enabling multiple slicer selection

amitchandak — Wed, 15 Jul 2020 16:57:11 GMT

arielcedola , Not sure it will help. But you can have slicer like this

Create a column like this in your date table

Month Type = Switch( True(),
year([Date]) = year(Today())-1,"Last Year" ,
year([Date])= year(Today()),"This Year" ,
Format([Date],"YYYY")
)

Now you save with "this year", so when you come to the page it will be the latest year. And change it also.

Re: How to set default value for slicer but enabling multiple slicer selection

mwegener — Thu, 06 Aug 2020 20:56:29 GMT

Hi arielcedola ,

was your problem solved?

Re: How to set default value for slicer but enabling multiple slicer selection

arielcedola — Tue, 11 Aug 2020 10:30:19 GMT

Hi mwegener

not yet. In another post Anonymous suggested me to use ISFILTERED() DAX function to know if the column is being filtered with the slicer. Only when it returns false I should set the default value for the year, it's clear. The point is that I'm not sure how to use this conditional instead of the previously used ranking to filter a table or a viz. Any suggestion?

Thanks 😉

Re: How to set default value for slicer but enabling multiple slicer selection

mwegener — Wed, 12 Aug 2020 06:30:10 GMT

Hi arielcedola ,

That is not easy...
Check out the solution in the appendix.

Re: How to set default value for slicer but enabling multiple slicer selection

arielcedola — Wed, 12 Aug 2020 09:24:47 GMT

Thanks mwegener

it works perfectly, thank you so much. Here your solution:

YearFilter = var _MAX_SalesYear = CALCULATE(MAX(Sales[Year]),ALL(Sales)) RETURN IF(ISFILTERED(Sales[Year]), 1, MAXX(Sales, IF(Sales[Year] = _MAX_SalesYear, 1, BLANK())))

Let me ask you something. MAXX function could be replaced without differences in the result for instance by MINX or MEDIANX, is it correct? Why is this function required? If ISFILTERED() is false you need to assign 1's to the rows where Year = _MAX_SalesYear, is this the way?

Re: How to set default value for slicer but enabling multiple slicer selection

mwegener — Wed, 12 Aug 2020 09:36:03 GMT

Hi arielcedola,

I use the iterator function to check the rows for the MAX year and to return a 1 accordingly.
The solution is not perfect, but it works in this scenario.

Re: How to set default value for slicer but enabling multiple slicer selection

arielcedola — Wed, 12 Aug 2020 10:10:48 GMT

This is the solution I was trying, it does the work too.

articleDefaultYear = var MaxYear = CALCULATE(MAX(Sales[Year]), ALL(Sales)) RETURN IF( not ISFILTERED(Sales[Year]), CALCULATE(MIN(Sales[Article]), FILTER(Sales, Sales[Year] = MaxYear)), CALCULATE(MIN(Sales[Article])) )

articleDefaultYear is not blank must be set as visual-level filter in the table for this to show only the current year data by default (if no year is selected with the slicer) or data from the selected year/years instead.