How to get date slicer to show data with no dates

[cheid_4838]

I have been asked to add a date slicer to the below driver scorecard dashboard.  The date is based on when an event/incident occured with each driver.  What they would like to do is select a date range so the dashboard shows how many events (Total Points) a driver had in a given period of time.  When I select the date range it filters to only those drivers with an incident.  Some drivers have had no incidents so there will be no event date and those drivers do not show up in the dashboard.  How do I get the slicer to show both the drivers with an incident and drivers without an incident since there is no event date listed for those drivers?

[Ritaf1983]

Hi @cheid_4838 
If you want to show these drivers with blank cells, you can use the functionality of "show items without data" 

Note that you need the drivers to be in "driver's" table :

If you want them with "0" then you can create a measure like :

accidents qty = DISTINCTCOUNT('Table'[issue id])+0

Result :

pbix with the example is attached

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

[Anonymous]

Hi @cheid_4838 

The slicer serves to filter the data.

You mentioned that “Some drivers have had no incidents so there will be no event date ”, so the data for those drivers will not be displayed in the date range selected by the slicer.

You can create a calculated column in the data model that can be used to assign default dates to drivers that do not have events, and include that date when filtering with the date slicer.

"Table"

Create a column.

Column = 
IF(
    ISBLANK('Table'[date]), 
    CALCULATE(MAX('Table'[date]), FILTER(ALL('Table'), 'Table'[driver] = EARLIER('Table'[driver]))), 
    'Table'[date]
)

Here is the result.

Regards,

Nono Chen

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

[cheid_4838]

I thought this would the solution, but for some reason it's not showing those drivers with no date.    I was expecting to see the data filtered down to those with event dates and without event dates, but that is not happening. I am not seeing drivers with no dates.

EventDt =

IF(

    ISBLANK('Driver Scores'[Event Date]),

    CALCULATE(MAX('Driver Scores'[Event Date]),FILTER(ALL('Driver Scores'),'Driver Scores'[Driver ID]=EARLIER('Driver Scores'[Driver ID]))),'Driver Scores'[Event Date])

[Anonymous]

Hi @cheid_4838 

Did you create slicer using the New column?

[cheid_4838]

Is there away to fill in the blank dates with the max date selected from the date slicer?  Is that feasible?