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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
apmarshxx
Frequent Visitor

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

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_0-1668490942434.png

 

2 REPLIES 2
apmarshxx
Frequent Visitor

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
Community Support
Community Support

Hi @apmarshxx ,

 

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

vjianbolimsft_0-1667456601252.png

Please try:

First, create a new table for slicer:

vjianbolimsft_1-1667456625788.png

Then create a slicer and turn on the single select:

vjianbolimsft_2-1667456670094.png

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:

vjianbolimsft_3-1667457012981.png

vjianbolimsft_4-1667457024758.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.