Forum Discussion
Solved
Return value based on condition
| ID | Title | Work Item Type | Hierarchy Path | Parent ID | Closed Date | |
| 7141 | Test -- | Epic | 7141 | |||
| 7142 | F1 | Feature | 7141|7142 | 7141 | ||
| 7143 | US-1 | User Story | 7141|7142|7143 | 7142 | ||
| 7144 | A001- task us1 | Task | 7141|7142|7143|7144 | 7143 | 2/8/2020 | |
| 7145 | US-2 | User Story | 7141|7142|7145 | 7142 | ||
| 7146 | B001 - check systems | Task | 7141|7142|7145|7146 | 7145 | 2/10/2020 | |
| 7147 | US-3 | User Story | 7141|7142|7147 | 7142 | ||
| 7148 | C001 - test | Task | 7141|7142|7147|7148 | 7147 | 3/9/2020 | |
| 7149 | C002 - test erp | Task | 7141|7142|7147|7149 | 7147 | 3/10/2020 | |
| 7150 | C003 - test sql | Task | 7141|7142|7147|7150 | 7147 | 3/11/2020 | |
| 7151 | C004 - test servers | Task | 7141|7142|7147|7151 | 7147 | 3/12/2020 | |
| 7152 | C005 - test os | Task | 7141|7142|7147|7152 | 7147 | ||
| 7153 | C006 - test network | Task | 7141|7142|7147|7153 | 7147 | ||
| 7154 | C007 - off | Task | 7141|7142|7147|7154 | 7147 | ||
| 7155 | C008 - bugs | Task | 7141|7142|7147|7155 | 7147 | ||
I want to calculate closed data of task (7151)< closed data (tasks before that particular tasks (7148,7149,7150))
>then it should return good
> if any any date field in the tasks are null then also it should return good
vijaykumarj19 , Try like this
new column = var _1 = countx(filter(table,[parent ID ] =earlier([parent ID ]) && [code] <earlier([code])),[ID] ) var _2 = countx(filter(table,[parent ID] =earlier([parent ID]) && [code] <earlier([code]) && [Closed Date ] <earlier([Closed Date ]) ),[ID] ) return if(_1 =_2 , "OK",blank())Or with additional columns
//additional column code = left([Title],len([Title])-1) // we can use parent ID sequence= right([Title]) new column = var _1 = countx(filter(table,[code] =earlier([code]) && [sequence] <earlier([sequence])),[ID] ) var _2 = countx(filter(table,[code] =earlier([code]) && [sequence] <earlier([sequence]) && [Closed Date ] <earlier([Closed Date ]) ),[ID] ) return if(_1 =_2 , "OK",blank())
3 Replies
vijaykumarj19 , I am not able to logic, how do you get three are before 7151 not others?
In title there is a prefix C003,C002,C001 by using these need to compare C004(7151 prefix)
vijaykumarj19 , Try like this
new column = var _1 = countx(filter(table,[parent ID ] =earlier([parent ID ]) && [code] <earlier([code])),[ID] ) var _2 = countx(filter(table,[parent ID] =earlier([parent ID]) && [code] <earlier([code]) && [Closed Date ] <earlier([Closed Date ]) ),[ID] ) return if(_1 =_2 , "OK",blank())Or with additional columns
//additional column code = left([Title],len([Title])-1) // we can use parent ID sequence= right([Title]) new column = var _1 = countx(filter(table,[code] =earlier([code]) && [sequence] <earlier([sequence])),[ID] ) var _2 = countx(filter(table,[code] =earlier([code]) && [sequence] <earlier([sequence]) && [Closed Date ] <earlier([Closed Date ]) ),[ID] ) return if(_1 =_2 , "OK",blank())
