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

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

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

      Thank you is working perfectly😀

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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