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,

     

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,

 

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:

     

     

    • vollmers's avatar
      vollmers
      Regular Visitor
      8 years ago

      you are awesome. i'm going to keep bugging you. congrats :)

       

      I have 100s of thousands of rows and thousands of unique combinations.

    • GlassShark1's avatar
      GlassShark1
      Helper III
      5 years ago

      This should be the solution - this is the closest i can see to an alternative to excel's COUNTIF function. Alot of the other solutions require you to specify each variable or create a seperate list - this allows the variables to change by row. Thanks 🙂