Convert SQL query into DAX expression

[pshetty05]

Hello people,

Hope you are doing well.

Can someone help me in converting this SQL query into DAX expression?

SQL - Case WHEN ((CLIENT_STATUS = 'SH' OR PARTICIPANT_STATUS IS NOT NULL) AND (TRUNC(LAD_EMAIL_OPEN) > SYSDATE -371)) OR
(CLIENT_STATUS <> 'SH' AND PARTICIPANT_STATUS IS NULL AND (TRUNC(LAD_EMAIL_OPEN) > SYSDATE -186)) THEN 'PASS' ELSE 'FAIL' END AS QUALITY_CHECK

While I have been able to work on the not null by using NOT(ISBLANK) but Truncate and Date functions are where I'm facing.

[AllisonKennedy]

@pshetty05  

As @Greg_Deckler  has already suggested, you might get better answers if you describe what you're trying to achieve, as not everyone in this forum is well versed in both SQL and DAX, so the DAX experts may even be able to find a more efficient way to solve than just a direct translation. 

I'm not strong in Oracle, but always keen to stretch my brain so here's my best attempt at a direct translation. 

This looks like a single case statement, so you could use an IF() function here instead of SWITCH, but I don't know your full requirements and SWITCH is the closest DAX equivalent to CASE, IF() would be the DAX for IIF in SQL, so I have kept true to your SQL code here.

I also don't fully understand your date requirements and why you need to truncate? You may be able to use the full date time value and change the number of days you subtract from it? You may also want/need to use the NOW() function instead or you may even be able to simplify this further, so if you can explain the reasoning we can provide better support. 

QUALITY CHECK =
SWITCH (
    TRUE (),
     (
         (
            table[CLIENT_STATUS] = "SH"
                || NOT ( ISBLANK ( table[PARTICIPANT_STATUS] ) )
        )
            && (
                DATE ( YEAR ( table[LAD_EMAIL_OPEN] ), MONTH ( table[LAD_EMAIL_OPEN] ), DAY ( table[LAD_EMAIL_OPEN] ) )
                    > TODAY () - 371
            )
    )
        || (
            table[CLIENT_STATUS] <> "SH"
                && ISBLANK ( table[PARTICIPANT_STATUS] )
                && (
                    DATE ( YEAR ( table[LAD_EMAIL_OPEN] ), MONTH ( table[LAD_EMAIL_OPEN] ), DAY ( table[LAD_EMAIL_OPEN] ) )
                        > TODAY () - 186
                )
        ), "PASS",
    "FAIL"
)

[pshetty05]

@AllisonKennedy Thank you! This logic too serves my purpose. I shall keep in mind to be more precise while posting a new question 🙂 

[amitchandak · Accepted solution]

@pshetty05 , Try a new column like

QUALITY_CHECK =
switch ( true(),
(([CLIENT_STATUS] = "SH" || not(isblank([PARTICIPANT_STATUS]))) && ([LAD_EMAIL_OPEN].date > today() -371))
([CLIENT_STATUS] <> "SH" && isblank([PARTICIPANT_STATUS]) && ([LAD_EMAIL_OPEN].date > today() -186)) ,
'PASS' , 'FAIL' )

[pshetty05]

@amitchandak Tried it in the similar way but by using IF

IF(((Core_Universe[INDV_CLIENT_STATUS] = "SH" || NOT(ISBLANK(Core_Universe[PARTICIPANT_STATUS])) && (Core_Universe[LAD_EMAIL_OPEN].[Date]>Today()-371)) ||

(Core_Universe[INDV_CLIENT_STATUS] <> "SH" && ISBLANK(Core_Universe[PARTICIPANT_STATUS]) && (Core_Universe[LAD_EMAIL_OPEN].[Date]>Today()-186))), "PASS", "FAIL")

Thank you 🙂 

[Greg_Deckler]

@pshetty05  - These generally work better if you post a sample of your data and the expected output. Generally, you are likely going to want a SWITCH(TRUE()...) statement in DAX or an if then else in Power Query. TRUNC is an Oracle statement that returns some sort of date, correct??

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[pshetty05]

Hi @Greg_Deckler  Thank for sharing the links, it would be really helpful!

Regarding my issue, Trunc function is used to truncate the time and retain the date.

I have a column LAD_EMAIL_OPEN which has both Date and Time, so I'm looking for a function similar to TRUNC() in oracle.

I also want to check if this date is within 371 days. Therefore, I have used SYSDATE - 371 is greater than LAD_EMAIL_OPEN where SYSDATE is the current date/system date.

Rest of the query, I can manage - It is only the date part where I'm stuck.

Hope that makes it clear!

[AllisonKennedy]

You can use the FORMAT() function in dax to convert date to text and truncate. You could use column name.[date] or you could use the date part functions YEAR, MONTH, DAY.

I still don't understand why it's necessary to truncate the time from this column, but I guess I don't need to understand that.

You can also add this truncated column in Power Query, in the Add Column tab in the ribbon there are some really handy buttons for getting date, time, hour, etc information from dates.