Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Help with a CountIf function in Power BI

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!

Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    Anonymous 

    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's avatar
    muhammad_786_1
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Thanks for the reply from ryan_mayu and muhammad_786_1.

     

    Anonymous , 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.

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    Anonymous 

    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's avatar
      muhammad_786_1
      Solution Supplier
      2 years ago

      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's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Thanks for the reply from ryan_mayu and muhammad_786_1.

     

    Anonymous , 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.