topic Re: SQL TO DAX in DAX Commands and Tips

SQL TO DAX

GMkk — Fri, 15 Mar 2024 15:39:02 GMT

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

Re: SQL TO DAX

bhanu_gautam — Fri, 15 Mar 2024 16:57:00 GMT

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

Re: SQL TO DAX

lbendlin — Fri, 15 Mar 2024 17:45:58 GMT

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-Forum/ba-p/963216

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/1447523