Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DAX Turn Around Time Calculation with different/multiple condition

Hi Guys, I'm in need assistance on how to convert this SQL code in DAX. I need to calculate TAT-Turn Around Time with a multiple condition. I have to create a new column for TAT0 and TAT1 (Measure or Column).

 

Below is the SQL code for TAT0 and TAT1

TAT0 = case when r1_created is null then NULL 
when r1_line like '%ABC%' and r1_status ='Completed' then cast(r1_created - receipt_dt as decimal(10,2)) else NULL end 

TAT1 = CASE WHEN r1_po is not null and r1_ended is not null and Upper(r1_status) = 'Completed' and (upper(r1_line) not like '%ABC%' AND upper(r1_line) <>'R_And')
and r2_po is null and r2_status is null then cast(r1_created - receipt_dt as decimal(10,2)) 
WHEN r1_po is not null and r1_ended is not null and Upper(r1_status) = 'Completed' and left(.r1_model,3)='CAB'
and r2_po is not null and r2_ended is not null and r2_status='Completed' then cast(r1_ended - r1_created as decimal(10,2)) 
ELSE 0 
END

 

Thank you in Advance.

1 Reply

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    2 years ago

    To convert the given SQL logic for TAT0 and TAT1 into DAX, you'll need to create calculated columns or measures based on the conditions specified. Below are the DAX expressions to replicate the SQL logic.

    TAT0 Calculation

    Here's how you can create the TAT0 column in DAX:

    TAT0 = 
IF (
    ISBLANK('YourTable'[r1_created]),
    BLANK(),
    IF (
        CONTAINSSTRING('YourTable'[r1_line], "ABC") &&
        'YourTable'[r1_status] = "Completed",
        DATEDIFF('YourTable'[r1_created], 'YourTable'[receipt_dt], DAY),
        BLANK()
    )
)

    TAT1 Calculation

    Here's the DAX expression for TAT1:

    TAT1 = 
IF (
    NOT ISBLANK('YourTable'[r1_po]) &&
    NOT ISBLANK('YourTable'[r1_ended]) &&
    UPPER('YourTable'[r1_status]) = "COMPLETED" &&
    (
        NOT CONTAINSSTRING(UPPER('YourTable'[r1_line]), "ABC") &&
        UPPER('YourTable'[r1_line]) <> "R_And"
    ) &&
    ISBLANK('YourTable'[r2_po]) &&
    ISBLANK('YourTable'[r2_status]),
    DATEDIFF('YourTable'[r1_created], 'YourTable'[receipt_dt], DAY),
    IF (
        NOT ISBLANK('YourTable'[r1_po]) &&
        NOT ISBLANK('YourTable'[r1_ended]) &&
        UPPER('YourTable'[r1_status]) = "COMPLETED" &&
        LEFT('YourTable'[r1_model], 3) = "CAB" &&
        NOT ISBLANK('YourTable'[r2_po]) &&
        NOT ISBLANK('YourTable'[r2_ended]) &&
        'YourTable'[r2_status] = "Completed",
        DATEDIFF('YourTable'[r1_ended], 'YourTable'[r1_created], DAY),
        0
    )
)