Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.