topic Re: Converting SQl logic to DAX in DAX Commands and Tips

Converting SQl logic to DAX

Anonymous — Wed, 03 Aug 2022 00:02:19 GMT

HI All,

Working on getting transfering this expression as a measue in a tabular model.

The SQL is accurate but running into issues on the measure.

SQL:

CASE
WHEN Sale_Type = 'Upsell'
AND Product IN('SaaS')
AND NULLIF( DATEDIFF( DD,Start_Date], ISNULL( Renewal_date],end_date ) ) + 1,0 ) > 364
THEN ARR
ELSE TotalPrice
END AS 'TotalContractualValue'

DAX Attempt:
SWITCH( Sale_Type = "Upsell" && Product IN ("SaaS")
&& DateDiff ( StartDate], IF( ISBLANK( RenewalDate), EndDate, RenewalDate, DAY ) + 1 > 364, ARR
TotalPrice ) )))

Unfortunately, no matter how I word this or break it down, I end up with the same warning message and error:

Severity Code Description Project File Line Suppression State
Operator or expression '( )' is not supported in this context.

Any thoughts?

Re: Converting SQl logic to DAX

amitchandak — Wed, 03 Aug 2022 03:46:24 GMT

Anonymous , Try this as new column Not measure

SWITCH( True() ,
[Sale_Type] = "Upsell" && [Product] IN ("SaaS")
&& DateDiff ( StartDate], IF( ISBLANK( RenewalDate), EndDate, RenewalDate), DAY ) + 1 > 364, [ARR],
[TotalPrice] )

Re: Converting SQl logic to DAX

Anonymous — Wed, 03 Aug 2022 13:47:39 GMT

Thank you amitchandak
Unfortunately, this needs to be a measure

Re: Converting SQl logic to DAX

Jameswalter — Thu, 04 Aug 2022 08:10:05 GMT

First, click on the DAX query button in SSMS.
This will launch the screen that lets you select and connect to the SSAS Server with the Tabular Database you install above.
Make sure you select the database you want to query.
Proceed to write and execute your DAX queries.

Regards,
J Wick

Re: Converting SQl logic to DAX

v-xiaotang — Tue, 06 Sep 2022 02:28:28 GMT

Hi Anonymous

Thanks for reaching out to us.

CASE
WHEN Sale_Type = 'Upsell'
AND Product IN('SaaS')
AND NULLIF( DATEDIFF( DD,Start_Date], ISNULL( Renewal_date],end_date ) ) + 1,0 ) > 364
THEN ARR
ELSE TotalPrice
END AS 'TotalContractualValue'

measure code:

Measure = var _datediff=DATEDIFF(MIN('Table'[Start_Date]),IF(ISBLANK(MIN('Table'[Renewal_date])),MIN('Table'[end_date]),MIN('Table'[Renewal_date])),DAY)+1 var _istrue= _datediff>364 return IF(MIN('Table'[Sale_Type])="Upsell" && MIN('Table'[Product]) in {"SaaS"} && _istrue, [ARR],[TotalPrice] )

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.