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! 

    • vollmers's avatar
      vollmers
      Regular Visitor
      8 years ago

      shucks.. not quite for what i want..

       

      I have 2 columns

       

      mark - blue

      mark - blue

      suzy - red

      suzy - blue

      john - yellow

      john - yellow

      mary - blue

       

      want a function in power bi that will give me the count of the # of times each combo exists.. to give this

       

       

      mark - blue - 2

      mark - blue - 2

      suzy - red - 1

      suzy - blue - 1

      john - yellow - 2

      john - yellow - 2

      mary - blue - 1

    • vollmers's avatar
      vollmers
      Regular Visitor
      8 years ago

      what i'm looking for is a COUNTIFS that shows the # of times that each combo shows up:

      INPUT:

       

      NAME - COLOR

      mark - red

      mark - red

      mark - blue

      john - red

      john - blue

      john - yellow

       

      OUTPUT:

       

      NAME - COLOR - COUNT

      mark - red - 2

      mark - red - 2

      mark - blue - 1

      john - red - 1

      john - blue - 1

      john - yellow - 1

      • Sean's avatar
        Sean
        Community Champion
        8 years ago

        If you just want the count you can achieve this without any Measures or Calculated Columns.

        Just create a Matrix.

        Place Name in Rows - place Color in Columns - finally place either Name or Color in Values and change to display Count.

         

         

        If you insist on having a COLUMN with this information

        Concatenate the possible Name-Color combinations in a column and then count the results of that column

        So first create a COLUMN:

        Column = 'Table'[Name]&"-"&"'Table'[Color]

        then create a COLUMN:

        Countifs Column = CALCULATE ( COUNTA ( 'Table'[Column] ), ALLEXCEPT('Table', 'Table'[Column] ) )

         

         

         

        That should do it! Good Luck! :smileyhappy: