Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Worked flawlessly. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
79 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |