Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 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!

needs
  • tamerj1's avatar
    tamerj1
    4 years ago

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

     

     

  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    Ok, please try

    CASH_IPD =
COUNTROWS (
    CALCULATETABLE (
        SUMMARIZE (
            BI_OSR_Revenues,
            BI_OSR_Revenues[Encounter_No],
            BI_OSR_Revenues[Payment_TypeName],
            BI_OSR_Revenues[InvoiceNo]
        ),
        BI_OSR_Revenues[Tran_Month] = 3,
        BI_OSR_Revenues[Tran_Year] = 2022,
        BI_OSR_Revenues[Payment_TypeName] = "CASH (IPD)",
        NOT ISBLANK ( BI_OSR_Revenues[admission_Date] )
    )
)