DAX table search to apply discount based on ranges

Shinka 131 Reputation points

Hi everyone,

I wish to create a DAX measure that uses sales information on one table and brings the discount % from another table. The catch is that the tables are not related and the discount varies depending on how much the client has bought.

Please find an excel example here:


Thanks in advance for any help

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,654 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points

    Hi @Shinka

    One way assuming the below Tables


    Measure Total Sales:

    Measure Discount:
    =MAXX (
    FILTER ( TableDiscount, TableDiscount[Threshold] <= TableSales[Total Sales] ),
    TableDiscount[Discount %]

    Corresponding sample availL here

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Herbert Seidenberg 1,191 Reputation points

    Excel 365 Pro Plus with Power Pivot and Power Query.
    Mostly done with "M" in PQ, not "DAX" in PP.

    0 comments No comments

  2. Emily Hua-MSFT 27,546 Reputation points


    My measure formula (measure 1) is as following, Lz-3068's formula is more simpler.
    SWITCH(TRUE(),[Sum of Amount]>=0&&[Sum of Amount]<1000,"0%",[Sum of Amount]<10000,"1%",[Sum of Amount]<50000,"5%",[Sum of Amount]<100000,"10%",[Sum of Amount]>=100000,"25%")


    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.