Use Cases:
- Find the [Current Price] based on the Sales[Order Date], using a Historic Pricing table with Effective Date and Unit Price columns
- Calculate the [Employee Bonus] based on upper and lower limits for Sales[Sales Amount] each month, using a Bonus table with Bonus Percent, Minimum Sales and Maximum Sales thresholds
- Find the [Pallet ID] for the first pallet that has enough inventory to fulfill the Sales[Order Qty], using an Inventory table with Pallet ID and Stock Qty columns
- So much more!
All these examples have one basic feature in common - there is no defined relationship between the two tables in question, and rarely will we find an exact match.
Employee Bonus Lookup
Let's focus on the Employee Bonus example for this post.
Given the Bonus table as follows:
We are asked to calculate the Employee Bonus amount based on their monthly sales figures defined as an aggregation on daily sales as below:
Looking at this table, we can see that EmployeeID 11115 is consistently earning a 5% bonus as their sales fall between $2,000-$20,000 each month.
EmployeeID 11117 however only gets 3% bonus for the months of September and October 2019.
Note: We have subtotals turned on in this visual so that we can see the annual total sales as well as the monthly sales.
Calculate Bonus %
As we already discussed, there is no defined relationship between these two tables, so if we try to add Bonus[Bonus] to our matrix above, we'll just get the same value for every row.
Instead, we can use DAX to perform an approximate lookup for us. Depending on your preference for functions and a bit on your business requirements, any of the below will get you close:
Bonus MAXX =
MAXX ( FILTER ( Bonus, [TotalSales] > Bonus[Min Sales] ), Bonus[Bonus] )
Bonus MINX =
MINX ( FILTER ( Bonus, [TotalSales] < Bonus[Max Sales] ), Bonus[Bonus] )
Bonus Between =
CALCULATE (
SELECTEDVALUE ( Bonus[Bonus] ),
FILTER (
Bonus,
Bonus[Min Sales] < [TotalSales]
&& Bonus[Max Sales] > [TotalSales]
)
)
Note the Bonus MINX does not really achieve what we want and all of them have the incorrect value for Bonus in the totals for the year.
What do we want to display as the annual bonus? In the case of EmployeeID 11116 the decision may be easier, but for EmployeeID 11117 it is not so straightforward. Since the bonuses are calculated and paid monthly, we will opt to return BLANK() when more than one month has been selected. This can be done on any of the Bonus options above simply by nesting it inside an IF statement to check that DimDate[YearMonthKey] has one value only:
Result
Bonus =
IF (
HASONEVALUE ( DimDate[YearMonthKey] ),
MAXX ( FILTER ( Bonus, [TotalSales] > Bonus[Min Sales] ), Bonus[Bonus] )
)
