Need help with lookup-like column with multiple combinations/variables.

Hi 84,

First: Create a classification Table Like the example lets assume it name is "ClassificationRules"

Second Step: Creating The Dax Mesure

Classification = 
VAR CurrentPlant = SELECTEDVALUE('Inventory'[Plant])
VAR CurrentMaterial = SELECTEDVALUE('Inventory'[Material Number])
VAR CurrentStorageLoc = SELECTEDVALUE('Inventory'[Storage Location])
VAR CurrentSpecialStock = SELECTEDVALUE('Inventory'[Special Stock Indicator])

VAR ClassificationRules = 
    FILTER(
        'ClassificationRules',
        'ClassificationRules'[Plant] = CurrentPlant
    )

VAR MatchingRules = 
    FILTER(
        ClassificationRules,
        (ISBLANK('ClassificationRules'[Material]) || 'ClassificationRules'[Material] = CurrentMaterial) &&
        (ISBLANK('ClassificationRules'[Storage Location]) || 'ClassificationRules'[Storage Location] = CurrentStorageLoc) &&
        (ISBLANK('ClassificationRules'[Special Stock]) || 'ClassificationRules'[Special Stock] = CurrentSpecialStock)
    )

// Count specificity - more specific rules (fewer blanks) get higher priority
VAR ScoredRules =
    ADDCOLUMNS(
        MatchingRules,
        "SpecificityScore",
        IF(NOT(ISBLANK('ClassificationRules'[Material])), 1, 0) +
        IF(NOT(ISBLANK('ClassificationRules'[Storage Location])), 1, 0) +
        IF(NOT(ISBLANK('ClassificationRules'[Special Stock])), 1, 0)
    )

VAR BestMatch = 
    TOPN(
        1,
        ScoredRules,
        [SpecificityScore], DESC,
        'ClassificationRules'[Classification], ASC
    )

RETURN
    IF(
        COUNTROWS(BestMatch) > 0,
        MAXX(BestMatch, 'ClassificationRules'[Classification]),
        BLANK()
    )

Give it a try 🙂
Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

84 

Create a small “Rules” table (one row per rule). Leave columns blank when they’re “don’t care”.

Rules columns:
• Plant (required)
• Material (nullable)
• Storage Location (nullable)
• Special Stock (nullable)
• Classification (text)
• Priority (number, optional – higher wins)

Then add a calculated column on Inventory:

Inventory[Classification] =
VAR RulesMatched =
FILTER (
Rules,
Rules[Plant] = Inventory[Plant]
&& ( ISBLANK ( Rules[Material] ) || Rules[Material] = Inventory[Material Number] )
&& ( ISBLANK ( Rules[Storage Location] ) || Rules[Storage Location] = Inventory[Storage Location] )
&& ( ISBLANK ( Rules[Special Stock] ) || Rules[Special Stock] = Inventory[Special Stock Indicator] )
)
VAR Ranked =
ADDCOLUMNS (
RulesMatched,
"SpecScore",
( IF ( NOT ISBLANK ( Rules[Material] ), 1, 0 )
+ IF ( NOT ISBLANK ( Rules[Storage Location] ), 1, 0 )
+ IF ( NOT ISBLANK ( Rules[Special Stock] ), 1, 0 ) ),
"Pri", COALESCE ( Rules[Priority], 0 )
)
RETURN
MAXX ( TOPN ( 1, Ranked, [Pri], DESC, [SpecScore], DESC, Rules[Index], ASC ), Rules[Classification] )

Notes:
• Plant-only rules work (others blank).
• More specific rules beat generic ones (SpecScore).
• Use Priority to override when needed.
• Add an Index column in Rules to break ties deterministically.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube