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.

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.