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

[arielcedola]

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.

[mwegener]

Hi @arielcedola ,

was your problem solved?

[arielcedola]

Hi @mwegener 

not yet. In another post @v-jayw-msft 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 😉 

[mwegener · Accepted solution]

Hi @arielcedola ,

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

[arielcedola]

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? 

[mwegener]

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.

[arielcedola]

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.

[amitchandak]

@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.

[lbendlin]

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