Create a checklist column based on criteria (if all items are available, then product is good)

[RustyNails]

Hello, I'm trying to create a calculated column which acts as a checklist for product. We're basically going to check if all the parts are available and in correct quantity. If true, then say "Ready", otherwise say "Not Ready".

Example:

Build Order ID	SKU	Qty	Build Status
P100001	33494	3	Ready
P100001	33495	6	Ready
P100001	33496	1	Ready
P100001	33664	2	Ready
P100002	33494	2	Not Ready
P100002	33495	6	Not Ready
P100002	33496	1	Not Ready
P100002	33664	2	Not Ready
P100003	33494	3	Not Ready
P100003	33496	1	Not Ready
P100003	33664	2	Not Ready

In this example, P100001 is the only product that is ready because it has the required SKUs, and in correct Quantity.

P100002 is not ready, because SKU# 33494 has only 2 parts qty instead of required 3. P100003 is also not ready because it's missing a SKU altogether.

Any pointers on how I can create a column for Build Status? Thanks

[BekahLoSurdo · Accepted solution]

Hi @RustyNails,

I created a column ("Quantity Audit") and a measure ("Build Status_Calculated"):

Quantity Audit = SWITCH(
    TRUE(),
    'Table'[SKU] = 33494 && 'Table'[Qty] = 3, 1,
    'Table'[SKU] = 33495 && 'Table'[Qty] = 6, 1,
    'Table'[SKU] = 33496 && 'Table'[Qty] = 1, 1,
    'Table'[SKU] = 33664 && 'Table'[Qty] = 2, 1,
    0 
)

Build Status_Calculated = IF( 
    CALCULATE( 
        SUMX( 'Table', 'Table'[Quantity Audit] ), 
        ALLEXCEPT( 'Table', 'Table'[Build Order ID] ) 
    ) = 4, 
    "Ready", 
    "Not Ready"
)

The column checks if each row meets its unique quantity requirements and the measure checks if each Build Order ID has enough qualified rows.

Hope this helps!

Bekah

[RustyNails]

 Worked flawlessly. Thank you!