PUG Exchange

1.  better way to do if statement

Posted 11 days ago
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 10 days ago
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 10 days ago
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

Posted 9 days ago
Edited by William Rodriguez 9 days ago
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 8 days ago
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 5 days ago
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

Posted yesterday
Hi Nikki:

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

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

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