Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Converting SQl logic to DAX

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?

 

  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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] )

  • Jameswalter's avatar
    Jameswalter
    New Member
    4 years ago

    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

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.