Counting number of States in a column using unique WorkItemId

[rbhole]

I want to count states from column. My data model as below,

WorkItemId	State	StateChangeDate	Column 2	New	Active	QA Ready	Closed	Done
87937	New	02-05-2022 12:51	02-05-2022 12:51
87937	Done	16-05-2022 06:57	02-05-2022 12:51					Done
87937	Active	17-05-2022 00:13	02-05-2022 12:51		Active
87937	New	18-05-2022 13:21	02-05-2022 12:51	New
87937	QA Ready	21-05-2022 04:23	02-05-2022 12:51			QA Ready
87937	New	23-05-2022 17:30	02-05-2022 12:51	New
87937	Active	13-06-2022 14:43	02-05-2022 12:51		Active

Note : I missed to enter Column kindly look it into expected output model.

In above model I filter only specific workitemid (of course in my model there is so many workitemid's).

I used to write DAX query to create other columns like New, Active, QA Ready, Closed, Done

Column 2 = CALCULATE (

    MIN ( History[StateChangeDate] ),

    ALLEXCEPT ( History, History[WorkItemId] )

)

New = if (CONTAINSSTRING(History[column],"New") && History[StateChangeDate] <> History[Column 2], "New" , "")

Active = if (CONTAINSSTRING(History[column],"Active") && History[StateChangeDate] <> History[Column 2], "Active" , "")

.

.

Done = if (CONTAINSSTRING(History[column],"Done") && History[StateChangeDate] <> History[Column 2], "Done" , "")

Column = CONCATENATE(History[WorkItemId], CONCATENATE(History[State], History[StateChangeDate]))

But I want to count each state, i expecting output like below,

WorkItemId	State	StateChangeDate	Column	Column 2	New	Active	QA Ready	Closed	Done
87937	New	02-05-2022 12:51	87937New5/2/2022 12:51:50 PM	02-05-2022 12:51	2	2	1	0	1
87937	Done	16-05-2022 06:57	87937Done5/16/2022 6:57:01 AM	02-05-2022 12:51	2	2	1	0	1
87937	Active	17-05-2022 00:13	87937Active5/17/2022 12:13:46 AM	02-05-2022 12:51	2	2	1	0	1
87937	New	18-05-2022 13:21	87937New5/18/2022 1:21:38 PM	02-05-2022 12:51	2	2	1	0	1
87937	QA Ready	21-05-2022 04:23	87937QA Ready5/21/2022 4:23:21 AM	02-05-2022 12:51	2	2	1	0	1
87937	New	23-05-2022 17:30	87937New5/23/2022 5:30:45 PM	02-05-2022 12:51	2	2	1	0	1
87937	Active	13-06-2022 14:43	87937Active6/13/2022 2:43:42 PM	02-05-2022 12:51	2	2	1	0	1

[Anonymous · Accepted solution]

Hi  @rbhole ,

Here are the steps you can follow：

1. Create measure.

New_Measure =
COUNTX(FILTER(ALL('Table'),
'Table'[New]<>BLANK()),[New])

Active_Measure =
COUNTX(FILTER(ALL('Table'),
'Table'[Active]<>BLANK()),[New])

Q Ready_Measure =
COUNTX(FILTER(ALL('Table'),
'Table'[QA Ready]<>BLANK()),[New])

Closed_Measure =
var _close=
COUNTX(FILTER(ALL('Table'),
'Table'[Closed]<>BLANK()),[New])
return
IF(
    _close = BLANK(),0,_close)

Done_Measure =
COUNTX(FILTER(ALL('Table'),
'Table'[Done]<>BLANK()),[New])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[lbendlin]

Your description is not clear to me.  Please describe in more details an with better examples what you are trying to achieve. 

A field change table usually has this format

Date | Field Name | Old Value | New Value

You may need to unpivot your data to bring it into that format.