Having below input, I need to create a DAX measure for a table with >1kk records that count (DISTINCTCOUNTNOTBLANK) Name for records that the minimum Date is higher than 20240101 however the Date equals 20240601
Input:
Date | Name | ID |
---|---|---|
20231201 | AAA | TEST |
20240101 | AAA | TEST |
20240201 | AAA | TEST |
20240501 | BBB | TEST |
20240601 | BBB | TEST |
20240601 | CCC | TEST |
20230101 | DDD | TEST |
Output:
ID | DISTINCTCOUNTNOTBLANK |
---|---|
TEST | 2 |
Only these records match the given requirements:
Date | Name | ID |
---|---|---|
20240601 | BBB | TEST |
20240601 | CCC | TEST |
In SQL:
with cte as (
select
"TEST" AS ID,
Name,
date,
min(date) over(partition by Name) as first_date
from
ZZZ
)
select
ID,
count(distinct Name)
from
cte
where
first_date >= 20240101
and date = concat(date_format(current_date(),'yyyyMM') , 01) -- 20240601
group by
ID
I have tried using below query but rather than this I need to adapt it to first calculate the min_date (logic as in MIN OVER) then add 2nd filter to get DateTable[Date] <= 20240601
MIN_VALUE =
VAR first_transaction = CALCULATE(MINX(DateTable, DateTable[Date]), DimMerchant)
RETURN
CALCULATE (DISTINCTCOUNTNOBLANK(NameTable[Name]),
FILTER(DateTable, first_transaction >= 20240101 && date = 20240601)
)
I can suggest something like this:
Measure = CALCULATE(
DISTINCTCOUNTNOBLANK('DataTable'[Name]),
FILTER(VALUES('DataTable'[Name]), CALCULATE(MIN('DataTable'[Date]), ALL('DataTable'[Date])) > "20240101"),
KEEPFILTERS('DataTable'[Date]= "20240601")
)
I've used ALL('DataTable'[Date]) and KEEPFILTERS() to alow this measure work correctly in a list like this:
If you are not planning to apply filters on Date somehow then you can use shorter one:
Measure = CALCULATE(
DISTINCTCOUNTNOBLANK('DataTable'[Name]),
FILTER(VALUES('DataTable'[Name]), CALCULATE(MIN('DataTable'[Date])) > "20240101"),
'DataTable'[Date]= "20240601"
)
But maybe someone offer more elegant solution..