Forum Discussion
DAX support -Column
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 |
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.
1 Reply
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.