98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Power BI training and help » Slicer that accounts with blank dates
Slicer that accounts with blank dates
Resolved · Medium Priority · Version 365
Grazi has attended:
Power BI Modelling, Visualisation and Publishing course
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
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.
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Page and Tab ShortcutsCtrl + Page Up/Page Down: Navigate between pages. |