Convert SQL query into DAX query

[yjk3140]

Hi,

I've posted a question in StackOver flow regareding how to convert SQL qery into DAX

Convert SQL query into DAX query for PowerBI visual - Stack Overflow

The answer I've recieved somewhat solved my query but doesn't meet my requirement 100%.

I need to dense rank the job id by timestamp in descending order and find the # of paused jobs.

Please see my comment under the response..

Would really appreciate any help!

[yjk3140]

Hi @amitchandak 

I applied this query but didn't get the correct result...

I wanna convert the sql query below into DAX, how can I correct the dax query you wrote?

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 and where t2.status = "PAUSED"

thanks for ur help 🙂

[amitchandak]

@yjk3140 , Check if this can help

Rankx(Allselected('Table'[Timestamp]) , CALCULATE( DISTINCTCOUNT('Table'[job_id]), 'Table'[status] = "PAUSED") ,,desc,dense)