This topic contains 3 replies, has 2 voices, and was last updated by 
-
Posts
-
I’m working with a file that has daily data for clinic visits. Each row in the report is a unique visit. I have been working on grouping the data by department ID and then the date so I can show aggregated totals for each day by department for specific metrics.
I loaded the data into Power Query and used the Group by function to group by DepartmentID and VisitDate and added a count of rows and a table for all rows as seen below.
What I’d like to do is add a few more custom columns that count the number of rows based on a specific value in a column.
For example, there’s a field called VisitStatus that indicates whether a visit was verified. I’d like to add a custom column that does a CountIF to show the total number of visits where the VisitStatus =”Verified” as seen in the screenshot below.
I attached a copy of my working file. I’m not sure which M functions to use, such as Table.RowCount or Text.Contains etc.
To clarify, I know that I can load this into PowerPivot and use CALCULATE but I’d like to aggregate these before I load the data into the data model.
Before aggregating, add a custom column with a formula like (I added a step to reorder the columns to make the grouping appear more intuitive):
= Table.AddColumn(#”Reordered Columns”, “IsVerified”, each if [VisitStatus] = “Verified” then 1 else 0)
Then when grouping, I added another column to the grouping that made a sum of the custom column.
Please see attached workbook.
Thank you Tom. What a simple and effective solution.
I posted this same question on the form at PowerBI.com a few weeks ago because I wasn’t able to add a new message here, and you should have seen the overly complex suggestions I received.
This will work well for my situation.
Thanks again,
Brian
The forum ‘Power Pivot’ is closed to new topics and replies.