cancel
Showing results for 
Search instead for 
Did you mean: 
0

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

Steps to replicate the behavior:

Step 1: Create the following 2 tables:

Table1:

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

Table2:

NumberNumberName
4four
5five
6six
7seven
8eight

 

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.

 

peterdi_0-1663849004889.png

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.

peterdi_1-1663849143069.png

Please let me know any possible solutions.

 

Status: Delivered

Hi @peterdi ,

 

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"

vmengzhumsft_0-1663911989799.png

2 choose "basic filtering"

vmengzhumsft_1-1663912024466.png

3 Uncheck the blank value

vmengzhumsft_2-1663912055461.png

4 Results show normal

vmengzhumsft_3-1663912132461.png

 

Best regards,

Community Support Team Selina zhu

 

Comments
v-mengzhu-msft
Community Support
Status changed to: Delivered

Hi @peterdi ,

 

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"

vmengzhumsft_0-1663911989799.png

2 choose "basic filtering"

vmengzhumsft_1-1663912024466.png

3 Uncheck the blank value

vmengzhumsft_2-1663912055461.png

4 Results show normal

vmengzhumsft_3-1663912132461.png

 

Best regards,

Community Support Team Selina zhu

 

peterdi
New Member

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

peterdi_0-1663925587624.png

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.

 

peterdi_1-1663925760335.png

 

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

 

Thank you again!