Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcarville
Skilled Sharer
Skilled Sharer

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 Company, and then depending on the Total SUM, apply a discount e.g. Company A have 1.8 Tons so they would get a 0.03 discount (discounts are all greater than).

 

I know I could do this by writing a large IF measure, but I'm wondering if there was a better way to do this.

 

Transactions:

CompanyDateTonsValue
A01/01/20160.535
A01/03/20161.3100
B01/03/20153256
C01/04/20154354
D01/03/20151.1123

 

DiscountCriteria:

TonsDiscount
0.20.01
0.50.02
10.03
30.04
50.05

 

Thanks,

Jake

5 REPLIES 5
Greg_Deckler
Super User
Super User

The smarter IF is called SWITCH.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

You can create another measure 

 

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

 

Just add table names to above formula

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.