slicer accounts blank dates

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Slicer that accounts with blank dates

Slicer that accounts with blank dates

resolvedResolved · Medium Priority · Version 365

Slicer that accounts with blank dates

Hello!

I have a cases table which has a 'close date'. I then created a date table like that:

CloseDateTable =
CALENDAR(MIN('cases'[dateClosed]), MAX('cases'[dateClosed]))

I did the relationship (1:M) and put the date table as a slicer. It works fine, but it ignores the blank values. The rows on the cases table that have a blank value on closed date means the case is still open and I'd like to show them.

I tried:
-'show items with no data' on the slicer, it doesn't work
- adding a blank row to the CloseDateTable, but then it doesn't allow me to do a relationship with the cases table - even though is only one row it says it has duplicate values

I'm stuck, any alternatives on how I can achieve this?

Thank you so much!

Grazi

RE: Slicer that accounts with blank dates

I hope the link works with a file sample of the issue. Thank you!

https://drive.google.com/file/d/1ZZLNEWK6kFfbq9Y3hhCaafB3W3C4uCcU/view?usp=drivesdk

RE: Slicer that accounts with blank dates

Hi Grazi,

Thank you for the forum question.

The reason for you cannot see cases without a Closed date is because the data has been removed in the connection.

In your Query Editor you have a step "Filtered Rows", where you tell Power BI you don't want the records where dateClosed is blank.

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([dateClosed] <> null))




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Edited on Wed 5 Jun 2024, 12:56

RE: Slicer that accounts with blank dates

I can't reply the last thread as I get an internal error 500 - just to let you know that the solution worked :-) Thank you!

Hi Jens,

Thanks for taking the time to look at it. Sorry, I left that filter there while I was testing. If you remove the filter it's still wrong and not counting it.

For example:
Non binary, closed date between 01/04/23 and 31/03/24 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've re-uploaded it without the filter, same link.

Thank you so much, Grazi

RE: Slicer that accounts with blank dates

Hi Grazi,

Please create a measure in your report. Copy below and past the DAX below in the formula bar.





count cases = countax('Cases',([CaseDate]>=FIRSTDATE('Cases'[CaseDate]))&&[CaseDate]<=LASTDATE('Cases'[CaseDate]))+CALCULATE( COUNTBLANK('Cases'[dateClosed]),all(CloseDateTable[Date]))-CALCULATE( COUNTBLANK('Cases'[dateClosed]),all('Cases'[CaseDate]),filter('Cases','Cases'[CaseDate]<>BLANK()))



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Thu 30 May 2024: Automatically marked as resolved.

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Page and Tab Shortcuts

Ctrl + Page Up/Page Down: Navigate between pages.
Ctrl + T: Create a new page.
Ctrl + Shift + F10: Toggle between fields pane and report canvas.

View all Power BI hints and tips


Server loaded in 0.08 secs.