Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Help Converting Excel Formula to DAX

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),"")))))

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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.

  • Brightsider's avatar
    Brightsider
    Resolver I
    2 years ago

    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. 🙂 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.