Slicer (between) is not working correctly

[Anonymous]

Hi,

My data set has FISCAL PERIOD column(DATA TYPE:WHOLE NUMBER) format 201501 to 201912,please see below when I have used fiscal period column in SLICER (BETWEEN) options.It shows some weird behaviour.

Thanks

[Anonymous]

Can you give some details on what exactly the "weird behaviors" are? Hard to troubleshoot without seeing some sample data and an explanation of exactly what the issue is.

Thanks,

Scott

[Anonymous]

Hi Scott,

Please review the screenshot,

Fiscal Period is showing for 201534 and 201871.In fiscal period column i have 201501,201502,201503,201504 to 201512.

Thanks

[Anonymous]

Ah got it. Sorry very limited time to try to help on the forums and when the problem isn't explicitely stated it's hard to troubleshoot.

My guess is that you have FY set up as a numeric field, thus getting the range slicer option. A range slicer like that is always going to give you all valid numeric values between the min and max. I'd try choosing just a list slicer instead of the range slicer. And/or change FY to be text instead of a number, in which case you won't see a range slicer even as an option.

Hope this helps,

Scott

[Anonymous]

Hi Scott,

Thank you so much.

As per my requirements,User wants to see option "BETWEEN" in slicer.

Thanks

[Anonymous · Accepted solution]

That isn't going to work if you are trying to use a slider style slicer, "between" on numbers is always going to give you all numbers. The slider will see "between 201801 and 201952" as saying "show me all numbers between the values of 201801 and 201952" - which includes numbers like 201899.

You could do something tricky like creating two copies of your date table, one called "starting date" and one called "ending date" and then using them as disconnected tables, coding your measures with DAX to work appropriately. But IMO this is a LOT of work for almost no benefit. The user requirement is to allow fiscal periods to be chosen, not to be forced into using a specific type of slicer (that doesn't work at all in this case).

Sorry, numeric slider slicers are meant to be used for things like "show me all products that cost between $500 and $1000" - where there might not be an exact price of $500 or $1000 in the data, so the numeric slicer has to allow users to input numbers that aren't in the data.

You could also of course just drop the date field in - but then users will see actual dates vs. your fiscal periods.

Hope this helps,

Scott

[Anonymous]

Thank you so much Scott for detailed explaniations,I was confused What and How to Explain.Now i am clear.