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()...
'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

bhanu_gautam's avatar
bhanu_gautam
Super User
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