Date table - slicer not showing blanks

[grazib123]

Hi all, I tried to solve this, but I'm not managing.

Case table - this has a closed date column and lots of it is blank. 

I created a date table using DAX - minimum (Case table.closedDate) to maximum (Case table.closedDate). 

I created a relationship and the closedDate is a filter / slice. It filters ok, but it removes the blanks; and I'd like to include them (as the case is open if the closedDate is blank). I put 'show items with no data' but it doesn't work. I tried adding a blank row in the date table, then I can't create a relationship as it says it's a M:M (even though there is only one blank row in the date table). 

What's the alternative to be able to do this?

Thank you! Grazi

[grazib123]

Anyone else has ideas on how I can do this? I tried everything and not managing to make it work... it should be a simple concept I'd think? Thank you! 

[lbendlin]

You have other data quality issues too

What are you ultimately trying to show? 

[grazib123]

@lbendlin yes, the data is bad and the users will clean it - I get it straight from a SQL Server connection. I'm not worrying with this yet. I need to get the numbers within the filter right, then push for them to cleanup. 

What I want is that the 'cases' table that have blank closedDate always show despite the closedDate slicer filter. 

What I have is 'right' except that it doesn't show when the closed date is blank.

For example:
Non binary, closed date between 01/04/23 and 31/03/24 count should be 3 - 2 with dates and one with blank closed date. But only 2 shows on the count (client ID 38563 is not showing).
I hope that makes sense... 

Thank you sooooo much! 🙂

[lbendlin]

I updated my reply a bit.

[grazib123]

Thank you! I downloaded the file and that's great if you don't have a filter, but as soon as I add a date slicer - based on your calendar, the 'blank rows' that you show disapear.

The logic I need is: show all cases where dateClosed is within the slicer date range OR blank. And the table breakdown is from the clients table so the date itself won't show. I can ignore the opened date... 

Thank you! 

[lbendlin · Accepted solution]

[grazib123]

Yes, that worked! 😄 thank you!
From what I see that calculated column you created works because the calendar2 table and the cases table don't have a relationship, is that right? I'll implement it on my file now, thanks again, I really appreciate it! 🙂

[grazib123]

Thank you both.

@lbendlin I didn't manage using the filter pane either, so I'm not sure what you mean? Thanks 

@Ashish_Mathur please find attached. I put the 'gender' breakdown to show what I mean, the 'non binary' for example, has 2 rows showing, but one case has a blank closedDate, so it should be 3. I can't see how to upload the file, so I've put it here: PBIX file 

Thank you again!

[lbendlin]

link asks for access, please validate

[grazib123]

@Ashish_Mathur sorry, just saw this, it's open now for anyone with the link, let me know if it doesn't work. Thanks again! 🙂

[Ashish_Mathur]

Hi,

That is strange.  When you drag the Date field from the Calendar table, blank should show up there becasue the Fact table has a blank in the Closing date field.  Share the download link of the PBI file.

[lbendlin]

Instead of a slicer use the filter pane. That's what it is there for. It can handle the blanks for you too.