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" and last_execution_datetime = (select max(last_execution_datetime) from table1 where s1.job_id = s2.job_id))
 
Paused_Jobs =
VAR latest_timestamp =
MAX('table1'[last_end_datetime])
VAR countid = CALCULATE(
DISTINCTCOUNT(table1[job_id]),
table1[job_active_status] = "PAUSED")        
VAR result = CALCULATE(countid, table1[last_end_datetime]= latest_timestamp)        
RETURN result
 
They give the same result but I'm not sure if the DAX query I wrote corresponds to my sql query logically and makes sense.
 
Could anyone please correct the query if it's wrong? thank you!
DAX Query
needs help

1 Reply

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