Column DAX

[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

[FreemanZ]

hi @prashantg364 

could you enrich your dataset to better present your case?

[ichavarria]

Here's one way to calculate the number of iterations required till the final resolution:

1. First, create a calculated column called "Iteration" using the following DAX formula:
2. Next, create another calculated column called "Iteration till final resolution" using the following DAX formula:

This formula will calculate the maximum iteration for each service ID where the service status is "resolution_2". The result will be the number of iterations required till the final resolution.

Make sure to replace 'your_table' with the actual name of your table in Power BI.

Best regards, 

Isaac Chavarria 


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