Help with a CountIf function in Power BI

[cpatton10]

Hello!

I have a Countif function in Excel that I need to replicate in PowerBI. 

Excel function: Countifs(B:B,B1,C:C,"Date In Service")

A        B                           C
2       Project A          Date In Service
1       Project B          Date In Service
1       Project C          Date In Service
1       Project D          Date In Service
2 Project A Date In Service

Essentially the excel function is counting how many "dates in service" there are per project. 

Column a is the output of the formula. 

I need help creating a similar DAX function.

Thank you!

[ryan_mayu · Accepted solution]

@cpatton10 

why 2 for project A? We only see one Date in Service for project A.

pls see if this is what you want

Column = countx(FILTER('Table','Table'[B]=EARLIER('Table'[B])&&'Table'[C]="Date In Service"),'Table'[B])

[muhammad_786_1 · Accepted solution]

You can create a calculated column like that in your table
Please see, if this is what you want

ColumnA =

CALCULATE(

    COUNTROWS(Project),

    FILTER(

        Project,

        Project[ColumnB] = EARLIER(Project[ColumnB]) &&

        Project[ColumnC] = "Date In Service"

    )

)

[Anonymous · Accepted solution]

Hi @cpatton10 

Thanks for the reply from ryan_mayu and muhammad_786_1.

@cpatton10 , you can create a calculated column as follows

Column = CALCULATE(COUNT('Table'[B]), FILTER(ALLEXCEPT('Table', 'Table'[B]), [C] = "Date In Service"))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@cpatton10 

why 2 for project A? We only see one Date in Service for project A.

pls see if this is what you want

Column = countx(FILTER('Table','Table'[B]=EARLIER('Table'[B])&&'Table'[C]="Date In Service"),'Table'[B])

[muhammad_786_1 · Accepted solution]

You can create a calculated column like that in your table
Please see, if this is what you want

ColumnA =

CALCULATE(

    COUNTROWS(Project),

    FILTER(

        Project,

        Project[ColumnB] = EARLIER(Project[ColumnB]) &&

        Project[ColumnC] = "Date In Service"

    )

)