SQL TO DAX

[GMkk]

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 

[lbendlin]

Ignore SQL and embrace DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[bhanu_gautam · Accepted solution]

@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