Convert SQL query into DAX

[yjk3140]

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!

[tamerj1]

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