Power BI Exchange

Expand all | Collapse all

better way to do if statement

  • 1.  better way to do if statement

    Posted 01-09-2018 05:09 AM
    Hi
    Please can you tell me if there is a better way to write this

    =IF ( [StockUom] = "TON" , [QtyInvoiced], =IF ( [StockUom] = "TON" , [QtyInvoiced],
                        IF( [AlternateUom] = "TON" && [ConvMulDiv] = "D" , [QtyInvoiced] * [ConvFactAltUom] ,
                                  IF( [AlternateUom] = "TON" && [ConvMulDiv] <> "D" , DIVIDE( [QtyInvoiced] , [ConvFactAltUom]),
                                         IF( [OtherUom] = "TON" && [ConvMulDiv] = "D" , [QtyInvoiced] * [ConvFactOthUom],
                                               IF( [OtherUom] = "TON" && [ConvMulDiv] = "D" , DIVIDE([QtyInvoiced] , [ConvFactOthUom]),1 )
                                               )
                                          )
                              )
          )

    ------------------------------
    Nikki Mackillican
    1501
    Britehouse
    Benoni
    0827791642
    ------------------------------


  • 2.  RE: better way to do if statement

    Posted 01-10-2018 06:50 AM
    Hi Nikki,
    Can you please try to use "switch" operator in DAX ? Here is the reference: https://msdn.microsoft.com/en-us/library/gg492166.aspx
    In case where you have multiple nested IF statements, you can move to switch.

    ------------------------------
    Md Tanvir Alam Anik
    Business Intelligence Specialist and Data Scientist.
    MIS, ACI Limited.
    ------------------------------



  • 3.  RE: better way to do if statement

    Posted 01-10-2018 09:26 AM
    You can try this, it is cleaner but I am not positive it is what you are looking for.

    =IF ( [StockUom] = "TON" , [QtyInvoiced],
                             IF( [AlternateUom] = "TON" || [OtherUom] = "TON",
                                              IF([ConvMulDiv] = "D", [QtyInvoiced]*[ConvFactAltUom], DIVIDE([QtyInvoiced], [ConvFactOthUom])) , 1), 1)

    ------------------------------
    Anthony
    ------------------------------



  • 4.  RE: better way to do if statement

    Silver Contributor
    Posted 01-11-2018 09:48 AM
    Edited by William Rodriguez 01-11-2018 09:58 AM
    Piggy-backing off Tanvir, See below for a 'Switch True' statement.

    There is almost no performance difference between 'Switch True' and 'If' statements (Switch uses IF); using Switch makes the code easier to read & write. I would recommend isolating the logic, separating them into different variables, and then inserting them into the Switch statement (not shown below). This would yield better performance and readability. See the following for more information:

    SWITCH STATEMENT:
    (Run the following code through DAX Formatter by SQLBI )

    =
    SWITCH(
        TRUE() ,
        [StockUom] = "TON" ,
        [QtyInvoiced],
        [AlternateUom] = "TON" && [ConvMulDiv] = "D" ,
        [QtyInvoiced] * [ConvFactAltUom] ,
        [AlternateUom] = "TON" && [ConvMulDiv] <> "D" ,
        DIVIDE( [QtyInvoiced] , [ConvFactAltUom]),
        [OtherUom] = "TON" && [ConvMulDiv] = "D" ,
            [QtyInvoiced] * [ConvFactOthUom],
        [OtherUom] = "TON" && [ConvMulDiv] <> "D" ,
        DIVIDE([QtyInvoiced] , [ConvFactOthUom]),
        1
    )

    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------



  • 5.  RE: better way to do if statement

    Posted 01-12-2018 07:29 PM
    Hi Nikki,

    As others have said, SWITCH is the way to go. However, be careful because it can have a dramatic performance impact in the way that you're using it. DAX does not process conditional statements very well. If your report is running slowly after changing to switch, try this technique.

    http://joyfulcraftsmen.com/blog/ssas-tabular-another-dax-ifswitch-performance-story/

    cheers
    Ben

    ------------------------------
    Ben Oastler
    Practise Director - North America
    +61411681138
    ------------------------------



  • 6.  RE: better way to do if statement

    Posted 01-15-2018 03:31 AM
    Thanks for the feedback guys.  I will post once I have tried it out to let you know how it turned out

    ------------------------------
    Nikki Mackillican
    1501
    Britehouse
    Benoni
    0827791642
    ------------------------------



  • 7.  RE: better way to do if statement

    Silver Contributor
    Posted 01-19-2018 11:17 AM
    Hi Nikki:

    Please make sure to mark the best answer as such; thanks!

    ------------------------------
    William Rodriguez
    Business Analyst

    ------------------------------