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
)

[BI-GreenApple]

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

[Anonymous]

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

[Anonymous]

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]

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

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

[tamerj1 · Accepted solution]

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

[Anonymous]

how to about this sir. how to convert to dax measure

select
count(ss.encounter_no) as CASH_IPD
from
(
SELECT distinct BT.encounter_no, BT.CreditInvoice_No, BT.Payment_Type, doc.Doctor_Code, bt.Tran_Month, bt.Tran_Year
FROM tbl_BIL_BillTrans_IPD_Archived BT
INNER JOIN dbo.tbl_MST_Doctor_Master dm WITH (NOLOCK)
ON bt.Doctor_Code = dm.Doctor_Code
inner join
(
select distinct t1.Encounter_No,
t1.Registration_No,
t1.Company_Code,
t1.Doctor_Code,
t1.Payment_Type,
t1.Payment_TypeName
from
(
select
Encounter_No,
Registration_No,
Company_Code,
Doctor_Code,
Payment_Type,
Payment_TypeName
from BI_OSR_Revenues
where Tran_Month = 1 and Tran_Year = 2022
and Payment_TypeName = 'CASH (IPD)'
and isnull(admission_Date,'') <> ''
) as t1 ) doc ON BT.Encounter_No = doc.Encounter_No and bt.Doctor_Code = doc.Doctor_Code
WHERE (bt.Tran_Month = 1)
AND bt.Tran_Year = 2022
AND bt.Payment_Type IN ('C')
AND bt.Status ='B'
) as ss

[Anonymous]

Thank you is working perfectly😀

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

[tamerj1]

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]

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. 

[yjk3140]

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]

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

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]

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. 

[yjk3140]

Hi @tamerj1 !

can we use count function intead of sum and if functions? 

sth like this..(not sure if this query would make sense)

Paused Jobs =

VAR MaxDate =

        MAX ( 'table1'[timestamp] )

VAR LastRecord =

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

VAR LastStatus =

        MAXX ( LastRecord, table1'[job_active_status] )

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

 RETURN countid

[yjk3140]

Hi @tamerj1 yes the query looks really clear but I'm not sure why it doesn't work on my dataset...

This is a screenshot of the actual table I have below

do you think the format of the date matters here..? if so, is there anyway to get the result I want with the timestamps in this format?

really appreciate your help..!

[tamerj1]

@yjk3140 
Is it a text or date format? 

[yjk3140]

Hi @tamerj1

It's a date/time format!

[tamerj1]

@yjk3140 
Please make sure you have this CALCULATE in your code