Converting SQL into DAX...

[yjk3140]

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

to get the number of paused jobs by getting the latest status of the each job_id. I have duplicate values in the table, so I can't use LASTDATE and MAX also didn't work to get the correct result.

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

this is my question posted in StackOverflow.

the solutions I got don't meet my requirements 100%

Just wondering if anyone knows how to convert that sql query to DAX.

I'm doing this in direct query mode so there are some limitations as well..

thanks in advance!

[tamerj1 · Accepted solution]

Hi @yjk3140 
Final solution is as follows

Paused Jobs =
SUMX (
    VALUES ( 'table1'[job_id] ),
    VAR MaxDate =
        CALCULATE ( MAX ( 'table1'[datetime1] ) )
    VAR LastRecord =
        FILTER (
            CALCULATETABLE ( 'table1' ),
            'table1'[datetime1] = MaxDate
                && 'table1'[job_active_status] = "PAUSED"
        )
    VAR Result =
        IF ( NOT ISEMPTY ( LastRecord ), 1 )
    RETURN
        Result
)

[yjk3140]

Hi @tamerj1 , 

I tried to use this query below to count the # of the jobs

Paused Jobs =

VAR MaxDate =

        CALCULATE(MAX ( 'table1'[timestamp] ))

VAR LastRecord =

        CALCULATETABLE ( 'table1', 'table1'[timestamp] = MaxDate )

VAR LastStatus =

        MAXX ( LastRecord, 'table1[job_status] )

VAR countid = CALCULATE( DISTINCTCOUNT(table1[job_id]), LastStatus = "PUASED")

 RETURN countid

and it's throwing the error below

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column

How can I fix it? 😕

[tamerj1]

Hi @yjk3140 
would you like to connect via teams or zoom? I have some time now

[yjk3140]

Hi @tamerj1  sure! I just created the zoom meeting and ID is 399 024 9510

[tamerj1]

@yjk3140 
I'll join in 5min

[yjk3140]

sure thank you 🙂

[tamerj1]

@yjk3140 

Asking for meeting passcode 

[yjk3140]

Hi @tamerj1! could you join again? the meeting ended because of the subscription issue..I just started the same one

[yjk3140]

///

[tamerj1]

@yjk3140 

Please send a new link