COUNTIF & COUNTIFS

[Kiewit]

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

Grouping	Item	Item Status	Grouping Status
BFW	1	Complete	INCOMPLETE
BFW	2	Complete	INCOMPLETE
BFW	3	Incomplete	INCOMPLETE
BFW	4	Incomplete	INCOMPLETE
HP NITROGEN	5	Complete	COMPLETE
HP NITROGEN	6	Complete	COMPLETE

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

All help is appreciated...

[Fowmy · Accepted solution]

@Kiewit 

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