Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help in converting SQL to DAX

Hi,

 

I am trying to convert the following SQL snippet into a DAX measure but got stuck as I am not sure how to handle DECODE.

 

SUM (
NVL (
ROUND (
NVL (Table.Retail_PNT, 0)
* DECODE (Item_Code, 'D', 0, 'I', 0, 1)
* CASE
WHEN Table.Amount* NVL ( (TO_DATE ( Table.Date_Time) - TO_DATE ( Table.Last_Movement_Date)), 28) < 1 THEN 0
ELSE ROUND ( NVL (Table.Amount, 0) * NVL ( (TO_DATE ( Table.Date_Time) - TO_DATE ( Table.Last_Movement_Date)), 0))
END
* DECODE (Table.Type_Code, 'SS', 0, 1)
,2)
,0))
AS Loss

 

Any help would be appreciated.

 

Thanks

Bob

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    New column=

    Var a=Round(if(Isblank(Table[Retail_PNT])=TRUE(),0,Table[Retail_PNT])* if(Table[Item_code] in {'"D","I"},0,1))

    Var t=if(Datadiff(Table[Date_Time],table[Last_Movement_Date],Day)

    <1,0,Round(if(Isblank(Table[Amount])=TRUE(),0,Table[Retail_PNT])*Datadiff(Table[Date_Time],table[Last_Movement_Date],Day))*if(table[Type code]="SS",0,1),2)

     

    Return

    If(a*t<>Blank(),a*t,0)

     

    i am not sure i have closed parenthesis correctly or not.

     

    Please check it from your side as well.

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.