Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
9 months ago
Solved

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

hi,     I have the following table   Domain    AppCode      TrainingAssigned ABC          123                null DEF           456                Complete DEF           999                nul...
Need Help
  • Zanqueta's avatar
    Zanqueta
    9 months ago

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

     

     

Zanqueta's avatar
Zanqueta
Super User
9 months ago

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's avatar
EaglesTony
Post Prodigy
9 months ago

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

  • Zanqueta's avatar
    Zanqueta
    Super User
    9 months ago

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