Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

 

 

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, 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
Super User

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

 

 

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.