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
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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors