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

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

yjk3140 
Is it a text or date format? 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    yjk3140 
    I'll join in 5min

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    Hi tamerj1

    It's a date/time format!

     

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    yjk3140 
    Please make sure you have this CALCULATE in your code

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    Hi tamerj1 , 

    I tried to use this query below to count the # of the jobs

    Paused Jobs =
    VAR MaxDate =
            CALCULATE(MAX ( 'table1'[timestamp] ))
    VAR LastRecord =
            CALCULATETABLE ( 'table1', 'table1'[timestamp] = MaxDate )
    VAR LastStatus =
            MAXX ( LastRecord, 'table1[job_status] )
    VAR countid = CALCULATE( DISTINCTCOUNT(table1[job_id]), LastStatus = "PUASED")
     RETURN countid
     
    and it's throwing the error below
    The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column
     
    How can I fix it? 😕
  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi yjk3140 
    would you like to connect via teams or zoom? I have some time now

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    Hi tamerj1  sure! I just created the zoom meeting and ID is 399 024 9510

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    sure thank you 🙂

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    yjk3140 

    Asking for meeting passcode 

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    ///

  • yjk3140's avatar
    yjk3140
    Helper I
    3 years ago

    Hi tamerj1! could you join again? the meeting ended because of the subscription issue..I just started the same one

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    yjk3140 

    Please send a new link