cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yjk3140
Helper I
Helper I

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!
1 REPLY 1
tamerj1
Super User
Super User

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

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors