Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mahenkj2
Solution Sage
Solution Sage

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

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.

 

mahenkj2_0-1714458582727.png

 

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.

1 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:

MFelix_0-1714483437283.png

 

MFelix_1-1714483451834.pngMFelix_2-1714483458547.png

 

MFelix_3-1714483490559.png

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

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:

MFelix_1-1714471290948.png

 

Has you can see the values on the slicer are persistent

 

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

MFelix_2-1714471452015.png

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.

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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?

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:

MFelix_0-1714483437283.png

 

MFelix_1-1714483451834.pngMFelix_2-1714483458547.png

 

MFelix_3-1714483490559.png

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:

mahenkj2_0-1714489343211.png

 

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?

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.

 

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.





Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.