Forum Discussion

Helper I

4 years ago

Solved

Converting SQL into DAX...

Hi, I have written the sql query below SELECT count(job_id) FROM (SELECT distinct job_id,status,DENSE_RANK over() partition by job_id order by timestamp desc AS rn FROM table1) t2 WHERE t2.rn = 1 a...

needs

yjk3140

Helper I

4 years ago

Hi tamerj1, tanks for your resoponse 🙂

I tried your query, but it gives me blank as a result...

do you think this dax query makes sense to get the correct result?

tamerj1

Community Champion

4 years ago

yjk3140

Apologies. It was too late last night and seems that I missed one small detail. Please use

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

and yes, for each job id we are finding the max date then we count it only if it is PAUSED

yjk3140
Helper I
4 years ago
Hi tamerj1! your DAX query looks logically correct and smart but I don't think MAX function works for the date column here..I'm not getting any result but blank.
Do you think there is another approach to solve this query that I can try?

Thank you so much for your help!
- tamerj1
  Community Champion
  4 years ago
  Hi yjk3140
  Just now I've prepared a sample file and it works just fine. However, this for sure depends of the filter context. The details that you might think they don't matter they actually do.
  
  Community 361.pbix37 KB
  - yjk3140
    Helper I
    4 years ago
    Hi tamerj1 yes the query looks really clear but I'm not sure why it doesn't work on my dataset...
    This is a screenshot of the actual table I have below
    do you think the format of the date matters here..? if so, is there anyway to get the result I want with the timestamps in this format?
    
    really appreciate your help..!