topic Re: Return value based on condition in DAX Commands and Tips

Return value based on condition

vijaykumarj19 — Thu, 06 Aug 2020 03:49:19 GMT

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

Re: Return value based on condition

amitchandak — Thu, 06 Aug 2020 04:12:53 GMT

vijaykumarj19 , I am not able to logic, how do you get three are before 7151 not others?

Re: Return value based on condition

vijaykumarj19 — Thu, 06 Aug 2020 04:19:12 GMT

In title there is a prefix C003,C002,C001 by using these need to compare C004(7151 prefix)

Re: Return value based on condition

amitchandak — Thu, 06 Aug 2020 05:00:27 GMT

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())