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

 

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

     

     

3 Replies

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