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

[apmarshxx]

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.

[apmarshxx]

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) 

[v-jianboli-msft]

Hi @apmarshxx ,

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.