topic Re: Help Converting Excel Formula to DAX in DAX Commands and Tips

Help Converting Excel Formula to DAX

Anonymous — Tue, 20 Aug 2024 14:48:52 GMT

Could someone please help me convert this excel formula to DAX? I was thinking I could use the Switch function but I am having trouble formatting it correctly.

IF([@InvoiceDate]>DATE(2020,12,31),
(IF([@[ProductType_Sub3]]="monthly",([@ExtensionAmt]*72),
(IF([@[ProductType_Sub3]]="yearly",([@ExtensionAmt]*6),
(IF([@[ProductType_Sub3]]="5 Year",([@ExtensionAmt]/5)*6,
(IF([@[ProductType_Sub3]]="4 Year",([@ExtensionAmt]/4)*6,
(IF([@[ProductType_Sub3]]="3 Year",([@ExtensionAmt]/3)*6,
(IF([@[ProductType_Sub3]]="2 Year",([@ExtensionAmt]/2)*6,)))))))))))),
(IF([@[ProductType_Sub3]]="monthly",([@ExtensionAmt]*36),
(IF([@[ProductType_Sub3]]="yearly",([@ExtensionAmt]*3),"")))))

Re: Help Converting Excel Formula to DAX

Greg_Deckler — Tue, 20 Aug 2024 14:51:08 GMT

Anonymous Technically the same IF syntax should technically work in DAX. That said, a SWITCH( TRUE(), ... ) statement would be preferred. That's a lot of nested IF statements though. It's hard to decipher with the poor formatting so I would start by properly formatting the IF statements and then it should be easier to figure out the logic.

Re: Help Converting Excel Formula to DAX

Brightsider — Tue, 20 Aug 2024 15:20:42 GMT

Try:

IF([InvoiceDate] > DATE(2020, 12, 31), SWITCH( TRUE, [ProductType_Sub3] = "monthly", ([ExtensionAmt * 72), [ProductType_Sub3] = "yearly", ([ExtensionAmt * 6), [ProductType_Sub3] = "5 Year", (([ExtensionAmt / 5) * 6), [ProductType_Sub3] = "4 Year", (([ExtensionAmt / 4) * 6), [ProductType_Sub3] = "3 Year", (([ExtensionAmt / 3) * 6), [ProductType_Sub3] = "2 Year", (([ExtensionAmt / 2) * 6), FALSE), SWITCH(TRUE, [ProductType_Sub3] = "monthly", ([ExtensionAmt * 36), [ProductType_Sub3] = "yearly", ([ExtensionAmt * 3), FALSE ))

I think captured what the formula's trying to do. You might have to monkey with it to get it to fit. 🙂

Re: Help Converting Excel Formula to DAX

Anonymous — Thu, 22 Aug 2024 09:43:08 GMT

Hi Anonymous , hello, Brightsider & Greg_Deckler , thank you for your promot reply!

We could also use the following measure to meet your requirement:

Result = SWITCH( TRUE(), SELECTEDVALUE('Table'[InvoiceDate])> DATE(2020, 12, 31), SWITCH( TRUE(), SELECTEDVALUE('Table'[ProductType_Sub3]) = "monthly", SELECTEDVALUE('Table'[ExtensionAmt]) * 72, SELECTEDVALUE('Table'[ProductType_Sub3]) = "yearly", SELECTEDVALUE('Table'[ExtensionAmt]) * 6, SELECTEDVALUE('Table'[ProductType_Sub3]) = "5 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 5) * 6, SELECTEDVALUE('Table'[ProductType_Sub3]) = "4 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 4) * 6, SELECTEDVALUE('Table'[ProductType_Sub3])= "3 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 3) * 6, SELECTEDVALUE('Table'[ProductType_Sub3]) = "2 Year", (SELECTEDVALUE('Table'[ExtensionAmt]) / 2) * 6, BLANK() ), SELECTEDVALUE('Table'[ProductType_Sub3]) = "monthly", SELECTEDVALUE('Table'[ExtensionAmt]) * 36, SELECTEDVALUE('Table'[ProductType_Sub3]) = "yearly", SELECTEDVALUE('Table'[ExtensionAmt]) * 3, BLANK() )

Best regards,

Joyce

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