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

[84]

I have hit a wall on trying to get the below working and could use some help. I hope I can explain it clearly.

On an existing dashboard, I have a Slow-Moving Inventory page. The users are requesting a way to classify the specific rows based on a few details; Plant, Material #, Storage Location and a Special Stock indicator.

The trick is each entry won't have all 4 facts. While the plant is always required, the classification could be all of a specific Material is Service parts or everything in a certain storage location is Customer Stock. It all depends on what the user determines.

Below is an example of a few classifications.

Plant	Material	Storage Location	Special Stock	Classification
19	7092750			Service Parts
20	GMB9357	0163		Last Time Buy
20		0820		Customer Stock
19	7088917		E	Vendor Stock

For 7092750: All plant 19 stock of that material regardless of storage location or Special stock is Service Parts.

For GMB9357: Plant 20 stock of that material only in storage location 0163 is a Last Time Buy.

For Storage Location 0820: All plant 20 stock in storage location 0820 is customer stock regardless of the material number.

For 7088917: All plant 19 stock in special stock E is Vendor stock, regardless of storage location.

Below is an example of the inventory with the Classification it would have.

INVENTORY

Plant	Material Number	Storage Location	Special Stock Indicator	Qty	Value	Classification
19	7092750	TRLR		5	$500	Service Parts
19	7092750	REPR		49	$245	Service Parts
19	7092750	CNTR		105	$210	Service Parts
20	GMB9357	0163		2	$39	Last Time Buy
20	GMB9357	0163		1	$14	Last Time Buy
20	GMB9357	0820		5	$279	Customer Stock
20	GMB9357	0820		7	$216	Customer Stock
20	AB-PCEU	0942		26	$3,145
19	7088917		E	6	$894	Vendor Stock
19	FGE8841	CNTR	E	2	$158	Vendor Stock

Getting this into DAX is stumping me big time, as there are so many variables/combinations possible. I will say that the Special Stock Indicator is the cherry on top, so if a solution exists without that field, it could work.

My current line of thinking is to use DAX to pull the classification into the Inventory table but open to any solution.

Thank you in advance for your help.

[Ahmed-Elfeel · Accepted solution]

Hi @84,

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

Plant	Material	Storage Location	Special Stock	Classification
19	7088917		E	Vendor Stock

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!

[v-tsaipranay]

Hi @84 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @84 ,

Thank you for reaching out to the Microsoft fabric community forum. 

I wanted to check if you had the opportunity to review the information provided by @Ahmed-Elfeel and @VahidDM thank you both for providing helpful resposne . Please feel free to contact us if you have any further questions.

Thank you.

[VahidDM · Accepted solution]

@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 

[84]

Thank you, this also works very well! This one took a little more setting up but has the benefit of being able to create a slicer without going through extra steps

[Ahmed-Elfeel · Accepted solution]

Hi @84,

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

Plant	Material	Storage Location	Special Stock	Classification
19	7088917		E	Vendor Stock

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]

Thank you @Ahmed-Elfeel, apologize for the delay in response.

This is great! I tested it with each of the possible variations and no issues. Thank you!