How do I get either 0 or a number based on a condition ?

[EaglesTony]

hi,

  I have the following table

Domain    AppCode      TrainingAssigned

ABC          123                null

DEF           456                Complete

DEF           999                null

DEF           777                In Progress

I am trying to find by Domain(group by) the number of AppCodes with TrainingAssigned=Complete, thus I would want something like:

Domain    TrainingAssignedTotal

ABC          0

DEF          1

Thanks

[Zanqueta]

Hello @EaglesTony,

To achieve this result in Power BI using DAX, you can create a measure that counts only the cases where TrainingAssigned = "Complete" and then use that measure in a visual grouped by Domain.

TrainingAssignedTotal =
CALCULATE(
    COUNT    COUNTROWS('YourTable'),
    'YourTable'[TrainingAssigned] = "Complete"

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[EaglesTony]

Thanks, but I'd like to do it in PowerQuery, as I want to eventually merge some tables together.

[Zanqueta · Accepted solution]

Ok @EaglesTony thanks for clarify.

so, can you use this expression and adapt for your case:

let
    // Sample data
    Source = Table.FromRows({
        {"ABC", 123, null},
        {"DEF", 456, "Complete"},
        {"DEF", 999, null},
        {"DEF", 777, "In Progress"}
    }, {"Domain", "AppCode", "TrainingAssigned"}),

    // Group by Domain and count only "Complete"
    Grouped = Table.Group(Source, {"Domain"}, {
        {"TrainingAssignedTotal", 
            each try List.Count(List.Select([TrainingAssigned], (x) => x = "Complete")) otherwise 0, 
            Int64.Type}
    })
in
Grouped

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.