Incorrect behavior for slicers with types: between, before and after

[Anonymous]

Steps to replicate the behavior:

Step 1: Create the following 2 tables:

Table1:

Number	NumberName
1	one
2	two
3	three
4	four
5	five
6	six
7	seven
8	eight
9	nine
10	ten

Table2:

Number	NumberName
4	four
5	five
6	six
7	seven
8	eight

Step 2: Create a 1-to-1 relationship between the 2 tables based on the Number columns.

Step 3: Create a table visualization with all columns for Table1 and a slicer for Table2's Number column.

Step 4: If it's not already selected, select 'Between' for type of the slicer.

When you try to slice Table1's data and in the slicer you select data from 4 to 8, every datapoint from Table1 will be returned.

Another strange behavior is present, when you try to select data from 4 to 6. After slicing, 7 and 8 will be missing from the table, but 9 and 10 will still be present.

Please let me know any possible solutions.

[v-mengzhu-msft]

Hi @Anonymous ,

Accorrding to my test, the reason for this situation is that there is a blank value in the number column of table2.

The solution is as follows.:

1 add number column into "filter on this visual"

2 choose "basic filtering"

3 Uncheck the blank value

4 Results show normal

Best regards,

Community Support Team Selina zhu

[Anonymous]

Hi @v-mengzhu-msft 

Thank you for your help, this is indeed solved this problem!

However, I encountered another issue of a similar matter with the between-type slicer, which I could not resolve with your solution.

I have a date table with dates from 1970-01-01 to today. For a report, I would like to create a date slicer with the last 7 calendar years, so the user can select any start and end date in this period. I created my slicer, and applied a relative date filter on it as in the image on the date column (Date Reference).

I created a table with only 1 column, the date column (Date Reference), but when the slicer's start date is 2015-01-01 and it's end date is 2021-12-31, the table will show all dates from 1970-01-01 to today.

I tried to apply your solution for this one, but even excluding blank value does not solve this.

Thank you again!