Why need to 'Clear selections' of date slicer when dates are extended much beyond actual data

[mahenkj2]

Hi,

I have been observing following. There are two date fields in main Table, which are connected with date table 'MainDate' and 'OtherDate'. Total rows in Table are 5, these 5 rows appear only when my both date slicers are cleared fully, if I disturb any one slicer by manually altering (even well beyound date range of that field in main Table) visible rows are reduced and they dont come back if I pull back slicer to end again. The only way to see all the rows is press clear selection button of the slicers.

I am unable to understand why it should happen this way and how to handle this situation.

Please find attached sample file:

https://drive.google.com/file/d/1DvyKgEDGcC_UkDUtKxk-Q75_B5Nd5HvC/view?usp=sharing

Thanks for your support.

[MFelix]

Hi @mahenkj2 ,

The problem you are facing is based on context, since you are making the selection it get pulled in to the filter context for both the slicer and they are used as a IN statemetn meaning that it's looking for the values within the both dates at the same time.

Explaining a little bit better when you don't have any selection you do not have any filters (the initial status of your slicer) when you start to move the slicer then the values get picked up and when drag it to the end of the slicer that value is maintain in the filter this generates a query similar to this:

Has you can see the values on the slicer are persistent

When you clear the filter you get to the initial result that is:

Has you can see there is no filter. Since where you have values for other and for invoice there is blank value you get an incomplet result.

Do you have any case where the Main Invoice date and the Other invoice date are both filled?

If not you can combine those two on a single column and have a single slicer that will allow you to do the correct filter everytime, in the PBIX file I used the table MainInvoice date as slicer but is working properly.

[mahenkj2]

Hi @MFelix 

Thanks for quick response.

I expected something similar, infact wanted to add more details of my actual scenario where both date column have situation when they have dates filled in and I can not merge them to connect with a universal date column. Did not indicate, just not to complicate much.

As a report developer, it looks no problem to me that I need to clear the slicer before making any inference of the report shown. Since this report is of finanicial purpose, user would not be comfortable in seeing the report based on date filters, and occassionaly clearing it the slicers to see if they are not missing any data.

Is there any other way, I can make a universal date column and then I will remove these two slicers, or use both slicer in some way, that selecting one slicers clears out another, if that makes sense in such scenarion?

[MFelix · Accepted solution]

Hi @mahenkj2 ,

In this case remove the relationships and use the following formula:

Filter Values = 
        VAR _DatesSelection = ALLSELECTED(MainDate[MainInvoiceDate])
		RETURN
			SUMX(
				FILTER(
					'Table',
					('Table'[Main invoice] IN _DatesSelection && NOT (ISBLANK('Table'[Main invoice])))
					||
					('Table'[Other invoice] IN _DatesSelection && NOT (ISBLANK('Table'[Other invoice])))
				),
				1
			)

Now use this has a filter in the visual:

I have added a line with both dates filled and you can see on the first image and on the last image the selections.

PBIX attached.

[mahenkj2]

Hi @MFelix 

Thanks, the solution you suggest in your sample file is as desired, I implemented this in my actual work, the filter visual measure indicate about 1500 rows to return in the table visual, but it takes little time to load and show this error:

Upon reducing date slicer, I can see the table visual properly. I think even if I use all the date, just 1500 rows is not large.

Is it due to date table stored in the date variable in the measure we created, but I can not reduce that, in fact with time that may even increase few years?

[mahenkj2]

Hi @MFelix ,

I found the reason, YYMM column from those date tables were yet the part of visual after removing existing table relationship.

Once I removed those fields from the visual, it is working as expected. Though, I would be fully satisfied if I could use date tables with relationships and not as disconnected tables and DAX measures. If there would be some ways to work with those slicers, it would be great.

[MFelix]

Hi @mahenkj2 ,

Not sure why you need the related tables, probably because of some other visualizations but you can also try the following measure:

Filter Values = 
        VAR _MainSelection = ALLSELECTED(MainDate[MainInvoiceDate])
        VAR _OtherSelection = ALLSELECTED(OtherDate[OtherInvoiceDate])
        var _FinalTable =CALCULATETABLE('Table', REMOVEFILTERS(MainDate[MainInvoiceDate]), REMOVEFILTERS(OtherDate[OtherInvoiceDate]))
		RETURN
			SUMX(
				FILTER(
					_FinalTable,
					('Table'[Main invoice] IN _MainSelection && NOT (ISBLANK('Table'[Main invoice])))
					||
					('Table'[Other invoice] IN _OtherSelection && NOT (ISBLANK('Table'[Other invoice])))
				 ),
				1
			)

Check PBIX file attach, sorry for giving this alternative only now.

[mahenkj2]

Hi @MFelix 

Appreciate. I think you suggest to use this alternate in place of earlier created measure. Whats potential advantage of this change? Is it to speed up mainly?