topic Re: Date slicer with toggle to include/exclude "blank" dates and plot points onto mapping visual in DAX Commands and Tips

Date slicer with toggle to include/exclude "blank" dates and plot points onto mapping visual

Anonymous — Tue, 15 Nov 2022 05:52:45 GMT

Hi All,

I am looking to create a map in which the points (long and lat.) are filterable by a date slicer and have a toggle that includes/excludes blank dates.

The issue concerns the treatment of blank/"null" dates whilst using slicer. I do not believe there is a solution.

I have tried:

- using a variable input from user (slicer outputting a value with with "selectedvalue" measure) - similar to this post: https://community.powerbi.com/t5/Desktop/OR-include-blank-values-in-date-slicer/td-p/959529

- restructuring data/queries (e.g. duplicating the data set three times over for each scenario)

- other

Simply having a drop-down list whereby the user selecs the date and "blank" checkbox isnt a neat solution when looking across years of dates.

Thanks,

****EDIT: I am aware of the method using measures as user-input variables - however to my knowledge this does not work for maps (long. lat. inputs) as the measure neds to return a table of circa 10,000 lat. and longs. which would take too much computing time when looking at a published dashboard product.

To give you an idea, the below is a small aea of a larger network. The date filter is being used on each of those segments (noted by colour here). Recalculating the measure every time a filter is touched will take too long (the final data set may be in the order of 1-2 million points).

My current approach is to exclude all blank dates and show on another page/tab.

Re: Date slicer with toggle to include/exclude "blank" dates and plot points onto mapping visual

v-jianboli-msft — Thu, 03 Nov 2022 06:39:34 GMT

Hi Anonymous ,

Based on your description, I have created a simple sample:

Please try:

First, create a new table for slicer:

Then create a slicer and turn on the single select:

Then apply the measure to the visual level filter:

Measure = IF ( SWITCH ( SELECTEDVALUE ( 'For Slicer'[Value] ), "Exclude", NOT ( ISBLANK ( MAX ( 'Table'[Date] ) ) ), "Include", TRUE () ), 1, 0 )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Date slicer with toggle to include/exclude "blank" dates and plot points onto mapping visual

Anonymous — Tue, 15 Nov 2022 05:31:35 GMT

Thanks v-jianboli-msft

Using measures as user-input variables works for other visuals. To my knowledge it does not work for plotting many points onto a map (i.e. putting a measure into the long and lat fields for say 10,000 points does not work (and if there is a way to make this work, calculating such a measure would make it too slow once published)