Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Stoo48
Frequent Visitor

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 works, all column headers are in the same table, as always any and all help gratefully received

 

Stuart

1 ACCEPTED SOLUTION

@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,

 

View solution in original post

12 REPLIES 12
Sean
Community Champion
Community Champion

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! Smiley Happy

Good Luck! Smiley Happy

Stoo48
Frequent Visitor

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

@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,

 

Dear v-sihou,

thsnk you for your solution. I've tried to apply your COUNTROWS FILTER combination, but it didn't work for me.. could you advise maybe, what do I do wrong?

thank you in advance for your help!

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Piechart-diagram-based-on-statistics...

Anonymous
Not applicable

How does the usage/formula differ if you leave out the CALCULATE part (something like below). With some similar formulas I have had similar results by using FILTER inside COUNTROWS. 

 

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

 

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

 

 

In the next case where C5 y F5 are variable:

 

=+COUNTIFS($C$2:$C$8,C5,$D$2:$D$8,F5)

 

How can i select a variable cell?

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
Community Champion
Community Champion

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.

 

Countifs Matrix.png

 

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] ) )

 

Countifs COLUMN.png 

 

That should do it! Good Luck! Smiley Happy

 

 

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 🙂 

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

 

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

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

Anonymous
Not applicable

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! 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.