Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

COUNTIF & COUNTIFS

Hi all,


I'm trying to replicate the following Excel formula in a column that lives in PowerBI, and I'm having some trouble. The formula should pull complete if all items for a certain grouping are complete. If only partial items are complete for the grouping, the result should be incomplete.

 

=IF(COUNTIF(A:A,A2)-COUNTIFS(A:A,A2,C:C,"COMPLETE")=0,"COMPLETE","INCOMPLETE")

 

GroupingItemItem StatusGrouping Status
BFW1CompleteINCOMPLETE
BFW2CompleteINCOMPLETE
BFW3IncompleteINCOMPLETE
BFW4IncompleteINCOMPLETE
HP NITROGEN5CompleteCOMPLETE
HP NITROGEN6CompleteCOMPLETE

 

Any idea how to replicate this formula in PowerBI when COUNTIF and COUNTIFS do not exist?

All help is appreciated...

  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Ads this as a column to your table: 

    Status = 
IF(
    CALCULATE( COUNTROWS(Table1) , Table1[Item Status] = "Incomplete", ALLEXCEPT(Table1,Table1[Grouping])) > 0,
    "Incomplete",
    "Complete"
)

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

1 Reply

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Ads this as a column to your table: 

    Status = 
IF(
    CALCULATE( COUNTROWS(Table1) , Table1[Item Status] = "Incomplete", ALLEXCEPT(Table1,Table1[Grouping])) > 0,
    "Incomplete",
    "Complete"
)

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn