Count how many times a distinct case has been in a certain state (multiple rows per case)

[CharliBrown]

Hi, 

Im new to PowerBI and are really stuck with an issue. I have a data sample size of 37.000 rows with cases. These cases have been in mulitple states through out their resolve time. I need to calculat how many times each case has been in a state, if its been in a state multiple times and how longe the case has been in the differnet states. 
Here is a example of my data:

I tried mulitple columnes calculations but nothing seams to give me the result i want. 

[FarhanJeelani · Accepted solution]

To analyze this data in Power BI, you can use DAX to calculate both the count of state transitions and the total time spent in each state for each case. Here’s a step-by-step guide to help you set it up.

Step 1: Import Data into Power BI
1. Load your data into Power BI by going to Home > Get Data and selecting your data source (Excel, CSV, etc.).

Step 2: Calculate the Number of Times Each Case has been in a State

1. Create a Measure to Count State Occurrences: Use a DAX measure to count the number of occurrences each case has been in a specific state.

DAX

StateCount =
COUNTROWS(FILTER(
TableName,
TableName[Case Number] = EARLIER(TableName[Case Number]) &&
TableName[State] = EARLIER(TableName[State])
))

Replace `TableName` with the actual name of your table in Power BI.

2. Add StateCount to a Visual: Use a table or matrix visual in Power BI, with Case Number and State on rows, and the StateCount measure as values. This will display the number of times each case has entered each state.

 Step 3: Calculate the Total Time in Each State for Each Case
1. Add a Column for Time in State: Use DAX to create a calculated column that calculates the duration of time each case has spent in a particular state. We can use the Start and End columns for this.

DAX

TimeInState =
DATEDIFF(
TableName[Start],
TableName[End],
MINUTE
)

This will give you the time spent in each state in minutes. You can change `MINUTE` to `HOUR` or `DAY` if a different time unit is needed.

2. Create a Measure to Sum Time in State: If you want to see the total time spent in each state for each case, create a measure that sums the `TimeInState` column:

DAX

TotalTimeInState =
CALCULATE(
SUM(TableName[TimeInState]),
ALLEXCEPT(TableName, TableName[Case Number], TableName[State])
)

3. Add TotalTimeInState to a Visual: Use this measure in a table or matrix visual along with Case Number and State to show the total time spent in each state for each case.

Step 4: Analyze Results
- You can create visuals like Bar Charts or Pie Charts to display the distribution of time spent across states or the frequency of state transitions.
- Filter the visuals by Case Number to analyze individual cases or by State to focus on specific states across cases.

[Anonymous · Accepted solution]

Hi @CharliBrown ,

Thanks FarhanJeelani  for sharing the method. I created some sample data based on your description and attached it to hopefully better help you with your problem.

Here is the data I created:

Create a calculated column:

StateCount = COUNTROWS(FILTER('Table', 'Table'[State] = EARLIER('Table'[State]) && 'Table'[Case Number] = EARLIER('Table'[Case Number])))

TotalTime =
VAR _duration = DATEDIFF('Table'[Start Time],'Table'[End Time],HOUR)
RETURN
_duration

Column =
CALCULATE(SUM('Table'[TotalTime]),FILTER('Table','Table'[Case Number] = EARLIER('Table'[Case Number]) && 'Table'[State] = EARLIER('Table'[State])))

The final result is shown below:

If this is not the result you wish to get. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

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

[FarhanJeelani · Accepted solution]

To analyze this data in Power BI, you can use DAX to calculate both the count of state transitions and the total time spent in each state for each case. Here’s a step-by-step guide to help you set it up.

Step 1: Import Data into Power BI
1. Load your data into Power BI by going to Home > Get Data and selecting your data source (Excel, CSV, etc.).

Step 2: Calculate the Number of Times Each Case has been in a State

1. Create a Measure to Count State Occurrences: Use a DAX measure to count the number of occurrences each case has been in a specific state.

DAX

StateCount =
COUNTROWS(FILTER(
TableName,
TableName[Case Number] = EARLIER(TableName[Case Number]) &&
TableName[State] = EARLIER(TableName[State])
))

Replace `TableName` with the actual name of your table in Power BI.

2. Add StateCount to a Visual: Use a table or matrix visual in Power BI, with Case Number and State on rows, and the StateCount measure as values. This will display the number of times each case has entered each state.

 Step 3: Calculate the Total Time in Each State for Each Case
1. Add a Column for Time in State: Use DAX to create a calculated column that calculates the duration of time each case has spent in a particular state. We can use the Start and End columns for this.

DAX

TimeInState =
DATEDIFF(
TableName[Start],
TableName[End],
MINUTE
)

This will give you the time spent in each state in minutes. You can change `MINUTE` to `HOUR` or `DAY` if a different time unit is needed.

2. Create a Measure to Sum Time in State: If you want to see the total time spent in each state for each case, create a measure that sums the `TimeInState` column:

DAX

TotalTimeInState =
CALCULATE(
SUM(TableName[TimeInState]),
ALLEXCEPT(TableName, TableName[Case Number], TableName[State])
)

3. Add TotalTimeInState to a Visual: Use this measure in a table or matrix visual along with Case Number and State to show the total time spent in each state for each case.

Step 4: Analyze Results
- You can create visuals like Bar Charts or Pie Charts to display the distribution of time spent across states or the frequency of state transitions.
- Filter the visuals by Case Number to analyze individual cases or by State to focus on specific states across cases.