Forum Discussion
Set flag on grouped order rows based on final order status
Hi I have a table with the first 4 columns. I would like to set a flag that indicates if an order has ended in a 'Reject' status. So if the last order status says 'Reject' flag it as Rejected. I can do this in SQL and upload to Power BI but struggling to write in DAX. Help is appreciated.
| Order ID | Status Start | Status Complete | Order Status | (Added Column) isRejected |
| 1 | 2017-08-21 | 2017-08-21 | 1 - Originator Matches to PO | 1 |
| 1 | 2017-08-21 | 2017-08-21 | 2b - A/P Enters Invoice | 1 |
| 1 | 2017-08-21 | 2017-08-21 | 4 - Capital Manager Approval | 1 |
| 1 | 2017-08-21 | 2017-08-21 | 1 - Originator Matches to PO | 1 |
| 1 | 2017-08-21 | 2017-08-21 | 1 - Originator Matches to PO | 1 |
| 1 | 2017-08-21 | 2017-08-22 | 7 - Reject | 1 |
| 2 | 2017-08-21 | 2017-08-21 | 1 - Originator Matches to PO | 0 |
| 2 | 2017-08-21 | 2017-08-21 | 2b - AP Enters Invoice | 0 |
| 2 | 2017-08-21 | 2017-08-23 | 3 - Property Accountant Review | 0 |
| 2 | 2017-08-23 | 2017-08-23 | 5 - Regional Manager Approval | 0 |
| 2 | 2017-08-23 | 2017-08-23 | 7 - Approve | 0 |
Hi Anonymous
Try this.
isRejected = VAR _reject = CALCULATE( COUNTROWS( 'Table' ), ALLEXCEPT( 'Table', 'Table'[Order ID] ), SEARCH( "Reject", 'Table'[Order Status] , 1, 0 ) > 0 ) RETURN IF( _reject > 0, 1, 0 )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
1 Reply
- Mariusz
Community Champion
Hi Anonymous
Try this.
isRejected = VAR _reject = CALCULATE( COUNTROWS( 'Table' ), ALLEXCEPT( 'Table', 'Table'[Order ID] ), SEARCH( "Reject", 'Table'[Order Status] , 1, 0 ) > 0 ) RETURN IF( _reject > 0, 1, 0 )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.