Approximate match with multiple criteria in DAX, part I

On a prior blog entry, I described how to use DAX to match the behavior of Excel’s VLOOKUP using approximate matches.   A few readers suggested I expand the example to include scenarios in which the approximate match should be used along with exact matches.

So here is the scenario: We want to calculate profit margin over products sold.  Here is the list of orders we received:

image

In order to calculate margin, we must find the cost for each product.  This information is stored in a separate table. The cost of producing each product changes over time, though, and the cost lookup table only registers a records for the days in which the cost changes:

image

Notice that in the Orders table there are sales for the fifth of January, whereas in the Cost Lookup table only cost records have been stored until the third.  We assume there is no other changes beyond the third.  As you probably already guessed, we must perform a dual lookup:  an exact match based on Product and Category and an approximate match based on Date.

Do not create a relationship between these two tables. Relationships are based on exact matches only, and we don’t need them here.

Following the same pattern applied in the approximate lookup blog entry, we attempt this solution:

=CALCULATE(
LASTNONBLANK( Lookup[Cost], 1 ),
FILTER(
Lookup,
Lookup[Category] = Orders[Category] &&
Lookup[Product] = Orders[Product] &&
Lookup[Date] <= Orders[Date]
)
)

Category and Product must be exact between both tables, while the date must be the last one for which there was a cost record in the lookup table.  When using this expression as a calculated column called [Cost], we get:

image

Notice there were two empty values as those products did not belong to a product/category combination that had a registered cost.  This could be a valid combination of product/category for which there was no valid data in the lookup table, or simply a bad record.  For all other records, there is a value.  However, we looking a bit closer, we realize the numbers are not correct.  For example if we filter only for records of Category A and Product P1, we get:

Orders

image

Lookup

image

As you can see, the calculated column in the Orders table should display 97 for both 1/3/2012 and 1/5/2012.  So why is this problem happening?  The issue has to do with the LASTNONBLANK function. Lets us go directly into the correct calculation:

=CALCULATE(
LASTNONBLANK( Lookup[Cost] , 1 ),
FILTER(
Lookup,
Lookup[Date] = CALCULATE(
LASTNONBLANK( Lookup[Date],1 ),
FILTER(
Lookup,
Lookup[Category] = Orders[Category] &&
Lookup[Product] = Orders[Product] &&
Lookup[Date] <= Orders[Date]
)
)
&&
Lookup[Category] = Orders[Category] &&
Lookup[Product] = Orders[Product]
)
)

Notice the modification to the prior formula is that we execute another lookup internally (colored in red). For those interested in learning more about the issue and the solution, read my next blog entry available here https://javierguillen.wordpress.com/2012/08/05/approximate-match-with-multiple-criteria-in-dax-part-ii/.

Now we are able to generate the approximate match and retrieve the valid cost.  An extra calculated column allows us to compute the Profit Margin:

=IF([Cost] <> BLANK(), [Price] – [Cost] )

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: