Search code examples
sqlpowerbidaxdaxstudio

Calculate first date similar to sql window function in DAX

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)
  )
Solution

  • 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:

    enter image description here

    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..