Forum Discussion

bdoucet's avatar
bdoucet
Regular Visitor
8 years ago
Solved

CountIf with Group by in Power Query

Hello,    I've seen a few threads on this but cannot find an answer for my problem    I have a table of visits and each row is a unique visit. Currently it's displaying all of the detail for each...
  • bdoucet's avatar
    bdoucet
    8 years ago

    I found a solution. 

     

    Before aggregating, I added a custom column with a formula like:

    = 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.

     

    Appears to be a simple solution that's working quite well. 

     

    Thanks to everyone for their suggestions.

parry2k's avatar
parry2k
Super User
8 years ago

why not filter by visit status first and then do the group by.

 

or this can be achieve by dax.

bdoucet's avatar
bdoucet
Regular Visitor
8 years ago

Hi Parry2k, 

 

Appreciate your response. The reason why I'm not filtering by Visit status first then doing a group by is because I'm using the countif for visitis verified to then divide by total visits to get a percentage of total visits verified.

 

I have several other fields besides the visit status that I'm looking to do this for.