Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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' )
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"
)
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 🙂
@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
@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.
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!
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
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |