Forum Discussion

Stoo48's avatar
Stoo48
Frequent Visitor
9 years ago
Solved

COUNTIFS Functionality in DAX

Trying to find a DAX equivilent to:   ACTIVITY =COUNTIFS([fault_code],[@[fault_code]],[equipment_id],[@[equipment_id]])   Have experimented with COUNT and CALCULATE but can't find a syntax that w...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Stoo48

     

    According to your description, you want to count rows which meet the criteria. Right?

     

    You can create a measure like below:

     

    COUNTIFS =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        Table,
        Table[fault_code] = fault_code1
            && Table[equipment_id] = equipment_id1
    )
)

    Regards,

     

Sean's avatar
Sean
Community Champion
9 years ago

Although I still use Excel I have not done anything in regular tables (meaning not PowerPivot tables) in years...

 

So with this caveat I belive this is the equivalent

 

Activity COLUMN =
IF ( 'Table'[fault_code] = BLANK () || 'Table'[equipment_id] = BLANK (), 0, 1 )

Hopefully this is the desired outcome! :smileyhappy:

Good Luck! :smileyhappy:

  • Stoo48's avatar
    Stoo48
    Frequent Visitor
    9 years ago

    Sean

     

    Thanks again, not quitre returning the results I was hoping for, its populated the retuurn column with a 1 or 0, similar to when you would build a truth table to conditionally format something, In excel the CONTIFS when set as described counts occurences of in this case Fault_Code.

     

    Many thanks for input, but back to the drawing board on this for now.

     

    Stuart

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

      Stoo48

       

      According to your description, you want to count rows which meet the criteria. Right?

       

      You can create a measure like below:

       

      COUNTIFS =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        Table,
        Table[fault_code] = fault_code1
            && Table[equipment_id] = equipment_id1
    )
)

      Regards,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi, I have been given an excel doc by a colleague they want translating into Power BI. They have used COUNTIF in excel, not sure if the same is required in PBI or not.

         

        Essentially we have a single table, with a list of incident numbers and a reporting month. I have created a column (in same table) to define whether the reporting month is, "Current Month", "Previous Month" or "Historic". 

         

        What I need to do now is calculate if an incident is still open using the following logic:

        - Incident number exists in Current Month and Previous Month then a value of "Open" is returned

        - Incident number does not exist in Current Month but exists in Previous Month then a value of "Closed in Period" is returned

         

        Any help would be gratefully received!