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