Product Pricing with Wildcards

Problem Explanation

Your problem involves determining the appropriate price for a product based on multiple columns (Category, Sub-Category, Product Group, Product) from a price file where wildcard matches (i.e., "*" for any value) are allowed.

Approach

To handle this, you'll need to create a method to dynamically search for the best match (most specific) to the least specific. Here's how you can achieve this in both DAX and Power Query (M).

DAX Approach

1. Create Calculated Columns in the Price Table that indicate if wildcards are used for each level.
2. Use LOOKUPVALUE or nested IFs to find the most specific price based on priority of columns.

PriceLookup =
VAR ProductCategory = 'Products'[Category]
VAR ProductSubCategory = 'Products'[Sub-Category]
VAR ProductGroup = 'Products'[Product Group]
VAR Product = 'Products'[Product]
RETURN
    CALCULATE(
        VALUES('PriceTable'[Price]),
        FILTER('PriceTable',
            (('PriceTable'[Category] = ProductCategory) || ('PriceTable'[Category] = "*"))  &&
            (('PriceTable'[Sub-Category] = ProductSubCategory) || ('PriceTable'[Sub-Category] = "*")) &&
            (('PriceTable'[Product Group] = ProductGroup) || ('PriceTable'[Product Group] = "*")) &&
            (('PriceTable'[Product] = Product) || ('PriceTable'[Product] = "*"))
        )
    )

• Explanation: This formula dynamically finds the price matching the product details, allowing for wildcards.

Power Query (M) Approach

1. Create a Function to Perform Matching. This function will attempt to find the closest match.
2. Apply the Function to Each Row in the Product Table.

Step 1: Create Matching Function

(priceTable as table, category as text, subCategory as text, productGroup as text, product as text) as number =>
let
    matches = Table.SelectRows(priceTable, each
        ([_Category] = category or [_Category] = "*") and
        ([_SubCategory] = subCategory or [_SubCategory] = "*") and
        ([_ProductGroup] = productGroup or [_ProductGroup] = "*") and
        ([_Product] = product or [_Product] = "*")
   ),
   sortedMatches = Table.Sort(matches,{{"_Category", Order.Ascending}, {"_SubCategory", Order.Ascending}, {"_ProductGroup", Order.Ascending}, {"_Product", Order.Ascending}}),
   bestMatch = sortedMatches{0}[_Price]
in
    bestMatch

Step 2: Apply Function to Each Row

let
    Source = ...,
    PriceTable = ...,
    AddPrice = Table.AddColumn(Source, "Price", each MatchingFunction(PriceTable, [Category], [SubCategory], [Product Group], [Product]))
in
    AddPrice

• Explanation: The Power Query approach defines a custom function to fetch the best matching price based on available data. It uses the logic to select rows that match or include wildcards and sorts them to find the most specific to least specific match.

Summary

Both DAX and Power Query approaches involve:

1. Iterating over possible combinations to match the product details with wildcard-enabled price data.
2. Ensuring the most specific match is chosen by considering precedence.
3. Creating either calculated columns/functions in DAX or custom functions in Power Query to achieve this efficiently.

For a deeper understanding and detailed learning paths, consider exploring the Enterprise DNA Platform's available courses on DAX and Power Query.