Forum Discussion

yjk3140's avatar
yjk3140
Helper I
3 years ago

Convert SQL query into DAX

Hi, I have converted the SQL query below into DAX  select count(job_id)from( select distinct job_id, job_active_status,last_execution_datetime from table1 s1 where job_active_status = "PAUSED" an...
DAX Query
needs help
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi @yjk3140 

please try

Paused Jobs =
SUMX (
    VALUES ( 'Table'[job_id] ),
    VAR MaxDate =
        MAX ( 'Table'[timestamp] )
    VAR LastRecord =
        CALCULATETABLE ( 'Table', 'Table'[timestamp] = MaxDate )
    VAR LastStatus =
        MAXX ( LastRecord, 'Table'[status] )
    RETURN
        IF ( LastStatus = "PAUSED", 1 )
)