Forum Discussion

jcarville's avatar
jcarville
Skilled Sharer
8 years ago

Smart IF

Hi,   I'm looking for a smart way, using a measure, to assign a discount value against a customer.   I have 2 tables Transactions and DiscountCriteria. I would like to SUM the Tons for each Compa...
Need Help
Show and Tell
Tips and Tricks
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

The smarter IF is called SWITCH.

jcarville's avatar
jcarville
Skilled Sharer
8 years ago

Thanks Greg_Deckler, I've gone with the tried and tested SWITCH(TRUE()) formula, how could I have forgot about this beauty:

 

Discount - Volume =
SWITCH (
    TRUE (),
    AND ( SUM ( SAGE_Extract[Tons] ) > 2, SUM ( SAGE_Extract[Tons] ) <= 5 ), 0.01,
    AND ( SUM ( SAGE_Extract[Tons] ) > 5, SUM ( SAGE_Extract[Tons] ) <= 9 ), 0.02,
    AND ( SUM ( SAGE_Extract[Tons] ) > 9, SUM ( SAGE_Extract[Tons] ) <= 12), 0.03,
    AND ( SUM ( SAGE_Extract[Tons] ) > 12, SUM ( SAGE_Extract[Tons] ) <= 14), 0.04,
    SUM ( SAGE_Extract[Tons] ) > 14, 0.05
)

 

This has raised the next issue for me. Every row in the Transactions table has a currency against it e.g. "USD" or "GBP". I would like to add something like this to my measure:

If the currency = "USD" then multiply the discount by my Currency Conversion Rate measure.

 

Any thoughts?

  • NipponSahore's avatar
    NipponSahore
    Resolver II
    8 years ago

    You can create another measure 

     

    AdjustedDiscunt =  if (currency = "USD", [Discount - Volume ]*[Currency Conversion Rate],[Discount - Volume ] )

     

    Just add table names to above formula

    • jcarville's avatar
      jcarville
      Skilled Sharer
      8 years ago

      NipponSahore, I'm pretty sure that formula only applies to a calculated column, not a measure.

      • jcarville's avatar
        jcarville
        Skilled Sharer
        8 years ago

        NipponSahore, I used a 2nd measure similar to this:

         

        Measure = IF(HASONEVALUE(SAGE_Extract[Symbol]),IF(VALUES(SAGE_Extract[Symbol]) = "USD",[Discount - Volume]*'Exchange Rate'[Exchange Rate Value],[Discount - Volume]))

         

        I might re-visit this in the future to see if I can combine the 2 measures into 1, but I am happy for now.