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' A...
  • 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.

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