Forum Discussion

GMkk's avatar
GMkk
Helper I
2 years ago
Solved

SQL TO DAX

Can someone help me in converting the sql where condition into dax 

WHERE      (E.STATUS = 'Active') AND 
(C.END_DATE IS NULL OR C.END_DATE >= CAST(GetDate() As date))AND
C.START_DATE <= GETDATE()
 
AND (C.CONTRACT_ID NOT IN (select TC2.CONTRACT_ID 
from fwatch.tblCONTRACTDETAILS as C2 LEFT JOIN
fwatch.tblEMPLOYEE as E2 ON C2.EMPLOYEE_ID = E2.ID LEFT JOIN
fwatch.tblTEMPCONTRACT as TC2 ON C2.TEMPCONTRACT_ID = TC2.ID
WHERE      (E2.STATUS = 'Active') AND 
(C2.END_DATE IS NULL OR C2.END_DATE >= CAST(GetDate() As date))AND
C2.START_DATE <= GETDATE()
AND TC2.CONTRACT_ID is NOT NULL
)
OR C.CONTRACT_ID is NULL
)

I wasn't sure , how to add the subquery part in dax 

'Need help' 'DAX'
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    GMkk , Try using filter formula

     

    FILTER (
    'YourTableName',
    'YourTableName'[Status] = "Active" &&
    ( ISBLANK ( 'YourTableName'[END_DATE] ) || 'YourTableName'[END_DATE] >= TODAY () ) &&
    'YourTableName'[START_DATE] <= TODAY () &&
    (
    ISBLANK ( 'YourTableName'[CONTRACT_ID] ) ||
    NOT (
    'YourTableName'[CONTRACT_ID] IN
    FILTER (
    'YourTableName',
    'YourTableName'[EMPLOYEE_STATUS] = "Active" &&
    ( ISBLANK ( 'YourTableName'[END_DATE] ) || 'YourTableName'[END_DATE] >= TODAY () ) &&
    'YourTableName'[START_DATE] <= TODAY () &&
    NOT ISBLANK ( 'YourTableName'[TEMPCONTRACT_ID] )
    )
    )
    )
    )

     

    Please accept as solution and give kudos if it helps

2 Replies