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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
grazib123
Regular Visitor

Date table - slicer not showing blanks

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

 

1 ACCEPTED SOLUTION

12 REPLIES 12
grazib123
Regular Visitor

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! 

You have other data quality issues too

 

lbendlin_0-1716322170437.png

 

What are you ultimately trying to show? 

lbendlin_1-1716322862956.png

 

@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! 🙂

I updated my reply a bit.

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

 

 

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
Regular Visitor

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!

link asks for access, please validate

@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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.