Grazi has attended:
Power BI Modelling, Visualisation and Publishing course
Power Automate Introduction 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
