Forum Discussion

yjk3140's avatar
yjk3140
Helper I
3 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
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
)

     

     

  • tamerj1's avatar
    tamerj1
    3 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] )
    )
)
tamerj1's avatar
tamerj1
Community Champion
3 years ago

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 )
)
yjk3140's avatar
yjk3140
Helper I
3 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's avatar
    tamerj1
    Community Champion
    3 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's avatar
      yjk3140
      Helper I
      3 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's avatar
        tamerj1
        Community Champion
        3 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.