November 27, 2017 at 8:35 pm #8670
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.
Attachments:You must be logged in to view attached files.November 27, 2017 at 8:49 pm #8672
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.November 28, 2017 at 6:24 pm #8681
- Started: 7
- Replies: 2545
- Total: 2552
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.
Attachments:You must be logged in to view attached files.November 28, 2017 at 9:20 pm #8687
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.
You must be logged in to reply to this topic.