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 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
    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] )
    )
)

29 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
    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 , I think if I understood your query correctly..you are finding the latest timestamp in the table then count the job_ids are in paused status with that timestamp. please correct me if I understood it wrong.. but what i want is to find the max timestamp for each job_id then if the status of the job is in paused status with its latest timestamp then count it as 1. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    How to convert this SQL Code to Dax Measure

     

    select count(Encounter_No) as CASH_IPD
    from
    (

    select distinct
    Encounter_No,
    Payment_TypeName,
    InvoiceNo
    from BI_OSR_Revenues
    where Tran_Month = 3 and Tran_Year = 2022
    and Payment_TypeName = 'CASH (IPD)'
    and isnull(admission_Date,'') <> ''
    ) as t1

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Anonymous 
      Please try

      CASH_IPD =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( BI_OSR_Revenues[Encounter_No] ),
        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] )
    )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        i did not get the right results because the sql query is having distinct fields

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    how to convert to DAX this sql statement

     

    set dateformat dmy
    select isnull(sum(visit),0) as VISIT_OPD, isnull(sum(followup),0) as followup from BI_OSR_Revenues
    where Tran_Year = 2019 and Tran_Month = 2
    and Payment_TypeName in
    ('CASH (OPD)','CREDIT BIL (OPD)','PACKAGE (OPD)','GOSI (OPD)')

  • BI-GreenApple's avatar
    BI-GreenApple
    New Member
    1 year ago

    How to convert this SQL to DAX

     

    SELECT CASE WHEN LEFT(value,1) = '[' THEN NULL ELSE LEFT(value,9) END AS ClientID, COUNT(*) AS [Count]
    FROM log
    CROSS APPLY STRING_SPLIT(REPLACE(DataSet,'"a_client":"','$'), '$')
    WHERE jobid in (SELECT Id FROM Jobs WHERE Name = 'Long Running Sessions')AND   CASE WHEN LEFT(value,1) = '[' THEN NULL ELSE LEFT(value,9) END IS NOT NULL
    AND statusid = 2
    AND LogDate >= DATEADD(DAY, -14, 2024-07-15) 
    GROUP BY CASE WHEN LEFT(value,1) = '[' THEN NULL ELSE LEFT(value,9) END
    ORDER BY COUNT(*) DESC