cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pshetty05
Helper I
Helper I

Convert SQL query into DAX expression

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.

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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' )

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User III
Super User III

@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"
)



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

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

amitchandak
Super User IV
Super User IV

@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' )

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@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
Super User IV
Super User IV

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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.


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors