DAX formula ignoring blank dates
Hello! I have a card that should display a number of 'all new clients during the dates selected on the top slicers'.
Cases table - have caseDate and closeDate.
It has client IDs that can be repeated and case ID.
I create on power BI 2 date tables to do slicers with caseDate and closeDate. That count also counts when the closeDate is blank. This is done with this DAX and works well:
I have a cases table that has client IDs. Slicers with a date table that fetches closed date and the same for open date.
All my counts through the report work fine and this is how I count it - it counts all distinct clients that belong to any case that wasn't closed during that time.
TotalClient = CALCULATE(DISTINCTCOUNT(' cases'[Client_ID]),union(filter(all(CloseDateTable),CloseDateTable[Date]=blank()),CloseDateTable))
It takes all cases that have a closed date after the date selected OR that closed date is blank. This works fine.
But what I want on that card that's different - it's that the client is 'new' meaning that case was created within the date period. This is my DAX - it then completely ignores that when the closed date is blank and it also ignores the case date...
Help! Thank you so much :-)
TotalNewClient2 =
CALCULATE(
DISTINCTCOUNT('cases'[Client_ID]),
UNION(
SELECTCOLUMNS(
FILTER(
ALL(CloseDateTable),
CloseDateTable[Date] = BLANK()
),
"Client_ID", count(' cases'[Client_ID])
),
SELECTCOLUMNS(
FILTER(
' cases',
' cases'[CaseDate] >= SELECTEDVALUE(CloseDateTable[Date])
),
"Client_ID", ' cases'[Client_ID]
)
)
)
RE: DAX formula ignoring blank dates
Hi Grazi,
Thank you for the forum question.
This is a classic Power BI issue:\ You want to count NEW clients (based on CaseDate) but still respect the logic that blank CloseDates = still active, and your current DAX is over engineered and breaking filter context.
You do NOT need UNION, SELECTCOLUMNS, or anything that complex.
Let’s simplify the logic first:
________________________________________
Goal
A measure that counts distinct clients where:
1. CaseDate is within the slicer-selected date range
2. CloseDate is either:
o after the selected CloseDate period
o OR blank
Your original “total clients” logic is okay, but for “new” clients, you just want to filter by CaseDate and still ignore CloseDate when blank.
This is MUCH easier than the path you’re on.
________________________________________
Correct DAX: Count New Clients Based on CaseDate, Ignoring Blank Close Dates
TotalNewClients
TotalNewClients :=
CALCULATE(
DISTINCTCOUNT('cases'[Client_ID]),
-- CaseDate must be inside the slicer range
KEEPFILTERS(
'cases'[CaseDate] >= MIN(CaseDateTable[Date]) &&
'cases'[CaseDate] <= MAX(CaseDateTable[Date])
),
-- CloseDate must NOT be filtered out if blank
KEEPFILTERS(
'cases'[CloseDate] >= MIN(CloseDateTable[Date])
|| ISBLANK('cases'[CloseDate])
)
)
________________________________________
Why this works
Respect slicers
Because slicers apply filter context automatically — you just need to use that context instead of removing it with ALL() or unions.
CaseDate filtering
The measure explicitly keeps only cases whose CaseDate is inside the slicer window.
Blank CloseDates included
ISBLANK('cases'[CloseDate]) ensures active/open cases are still counted.
No UNION needed
Your union logic broke filter context and returned a synthetic table unrelated to your actual rows.
________________________________________
If your CloseDate slicer uses a disconnected date table
This measure still works because:
• CaseDate filtering comes from CaseDateTable
• CloseDate filtering comes from CloseDateTable
• The OR/ISBLANK handles empty close dates even if the CloseDateTable has no blank row
________________________________________
Optional: much cleaner version if you prefer using TREATAS
TotalNewClients :=
VAR CaseDateFilter =
TREATAS(
VALUES(CaseDateTable[Date]),
'cases'[CaseDate]
)
VAR CloseDateFilter =
FILTER(
'cases',
'cases'[CloseDate] >= MIN(CloseDateTable[Date])
|| ISBLANK('cases'[CloseDate])
)
RETURN
CALCULATE(
DISTINCTCOUNT('cases'[ClientID]),
CaseDateFilter,
_CloseDateFilter
)
________________________________________
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: DAX formula ignoring blank dates
Hi Jens,
This totally makes sense, I've changed it and it still ignores the blank dates and the case date. It gets cases that were closed in that time period, but that's it. So I'm getting 35 instead of 19. Weirdly it's also counting the same client twice. Some of those 35 in the count is the same client (different cases).
I created a page just to cross check and output the true or false to see which cases were being included or not. I want to send you the screenshot, but I can't see how? Let me know and I'll share it with you.
Or any suggestions on what else I can try? It's so odd as it all seems right on the DAX formula.
Thank you so much,
Grazi
RE: DAX formula ignoring blank dates
Hi Jens,
I'm not sure you saw my reply above, can you please help? Do you think it could be a cache issue?
Thank you, Grazi
RE: DAX formula ignoring blank dates
Good Morning Grazi,
I am sorry but your follow up question has slipped through my fingers.
I apologise but I hope this can help you.
What you’re seeing is exactly what happens when the CloseDate slicer is still actively filtering the model, even though the DAX looks correct.
This is a very common (and very subtle) Power BI trap.
________________________________________
Root cause (this explains ALL symptoms)
Your CloseDate slicer is filtering the Cases table BEFORE your measure runs
So:
• Blank CloseDate cases are already removed
• CaseDate logic is never reached
• Same client appears multiple times because filtering is happening at case level, not client level
That’s why you see:
• 35 instead of 19
• Clients counted twice
• Only closed cases considered
No DAX inside CALCULATE() can “bring back” rows that were already filtered out by a slicer.
________________________________________
Fix: Explicitly REMOVE CloseDate slicer filter
You must turn off the CloseDate slicer for this measure, then re apply your own logic.
________________________________________
Final Correct Measure (THIS VERSION WILL WORK)
Total New Clients :=
VAR MinCaseDate =
MIN ( CaseDateTable[Date] )
VAR MaxCaseDate =
MAX ( CaseDateTable[Date] )
RETURN
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( 'cases'[Client_ID] ),
-- Client’s FIRST EVER case is in CaseDate slicer
CALCULATE (
MIN ( 'cases'[CaseDate] ),
ALLEXCEPT ( 'cases', 'cases'[Client_ID] )
) >= MinCaseDate
&&
CALCULATE (
MIN ( 'cases'[CaseDate] ),
ALLEXCEPT ( 'cases', 'cases'[Client_ID] )
) <= MaxCaseDate
)
),
-- THIS IS THE FIX
REMOVEFILTERS ( CloseDateTable )
)
________________________________________
Why this fixes everything
REMOVEFILTERS ( CloseDateTable )
• Completely disables the CloseDate slicer for this card
• Blank CloseDate cases are no longer silently removed
VALUES('cases'[Client_ID])
• Forces client-level granularity
• Guarantees each client counted only once
First-case logic
• Uses MIN(CaseDate) across all history
• Correctly identifies new clients
________________________________________
If you ALSO want “still active OR closed after X”
Add this inside the FILTER( VALUES(Client_ID) ) block:
&&
CALCULATE (
COUNTROWS ( 'cases' ),
FILTER (
'cases',
ISBLANK ( 'cases'[CloseDate] )
|| 'cases'[CloseDate] >= MIN ( CloseDateTable[Date] )
)
) > 0
________________________________________
Why your debug page showed weird True/False
Because:
• The slicer removed rows before the measure evaluated
• Your “test columns” were running in a filtered model
• So results looked illogical
Your instincts were right — the model wasn’t behaving how it appeared.
________________________________________
Checklist for future DAX debugging
When results:
• ignore BLANKs
• ignore MIN / MAX date logic
• double-count entities
Always ask:
“Is a slicer filtering the table before my measure runs?”
________________________________________
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
