Group by Completed Transactions

DAX DAX Calculations
1

Hello,

I’m trying to create a new column on a fact transaction table that counts how many surgeries each patient has had. Each patient has a unique client ID that can show up one or more times on this transaction table. Each surgery will have a unique ID (surgery ID). If one client has 3 surgeries, for example, the record will have the same client ID with 3 unique surgery IDs. “3” should show up on all records for that client ID.

I believe this should be done using a groupby, to analyze the entire table and count how many completed surgeries each client ID has had. I can then use this column as a slicer to analyze patient data by their count of completed surgeries.

I’m not sure if I have to do this in Query or with DAX. I understand that you can you GroupBy in query to count records, but I need to apply some conditions on the count (status = completed) rather than just count every record.

The first attachment is a sample data set
The second is how I want the completed surgery count grouby.

@BrianJ, this one seems similar to the other groupby post I had last month. Any ideas?

Surgery Data.csv (212.1 KB)

completed surgery count.xlsx (9.0 KB)

1 Like
2

@pete.langlois ,

IMO, these sort of grouping problems are almost always handled better through PQ. There are a lot of different way to address this in PQ - I chose a really straightforward approach that creates a separate table with the completed sums that then gets joined back to the main fact table. There are other ways to do this that don’t create the second table but are a bit more complicated. Because we’re not loading the temporary table to the data model, I didn’t see it as a problem, but you can let me know what you think. Here’s how I did it:

  1. Duplicated the main fact table
  2. In the temp table, filtered out every row that wasn’t a completed surgery
  3. Did the following Groupby on temp table:

image
image1748×785 32.1 KB

  1. Did a full outer join between the main fact table and the temp table to pull the Completed Surgeries count column into the main fact table
  2. Replaced nulls in the resulting fact table Completed Surgeries columns with 0
  3. Unloaded the temp table from the data model

Bingo! Here’s the result:

image
image964×739 102 KB

Thanks for the great job laying out the problem and providing all the necessary data and mockup of the result. That makes it easy to provide good support.

I hope this is helpful Full solution file attached.

3 Likes
3

Thanks, that’s awesome. And what if new records are added to the main fact table? Is there any way to ensure that the table is updated accordingly? IE if one of the client ID’s completes another surgery at some point, I would for their surgery count field to be updated to reflect the new surgery count.

4

@pete.langlois,

Yes, this solution will update every time you hit Refresh or reopen the report to include the new data and update the counts.

  • Brian
5

Great, thank you again!