DAX support -Column

[prashantg364]

I am having a table as shown below

date	Serviceid	servicestatus
6-2-23	1001	complaint_1
11-2-23	1002	complaint_2
25-2-23	1003	complaint_3
28-2-23	1004	resolution_1
3-3-23	1005	complaint_4
3-3-23	1006	resolution_2

I want to add a column at the end to calculate the number of iterations required till the final resolution.
Calculate all complaint iterations and consider only 1 iteration for the final resolution(resoultion_2 is considered as final in the below table)

date	Serviceid	servicestatus	Iteration till final resolution
6-2-23	1001	complaint_1	5
11-2-23	1002	complaint_2	5
25-2-23	1003	complaint_3	5
28-2-23	1004	resolution_1	5
3-3-23	1005	complaint_4	5
3-3-23	1006	resolution_2	5

[v-zhangti · Accepted solution]

Hi, @prashantg364 

You can try the following methods.

Result = 
Var _N1=CALCULATE(COUNT('Table'[Serviceid]),FILTER('Table',SEARCH("complaint",[Servicestatus],,0)<>0))
Var _N2=CALCULATE(COUNT('Table'[Serviceid]),FILTER('Table',SEARCH("resolution",[Servicestatus],,0)<>0))
Return
IF(_N2<>BLANK(),_N1+1,BLANK())

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.