topic Re: Show Dates that blank irrespective of the date slicer selection in DAX Commands and Tips

Show Dates that blank irrespective of the date slicer selection

AnthonyJoseph — Thu, 16 Dec 2021 11:48:41 GMT

Hi,

I have a table with 4 columns.

I want to have "Completed or Stat date" column as date slicer and my table visual should show the rows with the blank date (irrespective of date slicer selection) along with the rows falling in the selected date.

For example:
If I filter by dates 1 - June-21 to 31-Dec-21 in "Completed or Stat date".
My table should show all the rows that are in the date range (1 - June-21 to 31-Dec-21) and also the row items that have a blank value in the "Completed or Stat date".

Thanks,

Alex

Re: Show Dates that blank irrespective of the date slicer selection

bcdobbs — Thu, 16 Dec 2021 13:12:22 GMT

I would do the following.

1) Create a disconnected date table (if Power Bi puts a relationship in delete it)

Date = CALENDARAUTO(12)

2) Add a measure to your table (I called mine Task):

Date Filter = VAR SelectedDates = CALCULATETABLE ( VALUES( Task[Completed or Stat Date]), TREATAS ( VALUES ( 'Date'[Date] ), Task[Completed or Stat Date] ) ) VAR RowsToInclude = FILTER ( VALUES( Task[Completed or Stat Date] ), SelectedDates || ISBLANK( Task[Completed or Stat Date] ) ) VAR ShowDate = INT ( NOT ( ISEMPTY ( RowsToInclude ) ) ) RETURN ShowDate

3) Use the date column from your new date table in the slicer.

4) In your table visual put the "Date Filter" measure into the visual filter pane and set it to "Is 1"

I'm getting I think your desired results:

Re: Show Dates that blank irrespective of the date slicer selection

AnthonyJoseph — Thu, 16 Dec 2021 18:03:38 GMT

Works perfectly as expected. Thank you so much 🙂

Re: Show Dates that blank irrespective of the date slicer selection

Kate_Lashko — Fri, 26 Jul 2024 14:19:32 GMT

Had the same issue. Your solution works! Thank you