cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Regular Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Member

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

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

2 REPLIES 2
Member

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

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

Regular Visitor

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

Worked flawlessly. Thank you!

## Helpful resources

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 106 members 1,459 guests
Recent signins:
Please welcome our newest community members: