topic Re: COUNTIFS Functionality in DAX in Desktop

COUNTIFS Functionality in DAX

Stoo48 — Tue, 07 Feb 2017 01:23:54 GMT

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

Re: COUNTIFS Functionality in DAX

Sean — Tue, 07 Feb 2017 02:05:12 GMT

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:

Re: COUNTIFS Functionality in DAX

Stoo48 — Tue, 07 Feb 2017 13:38:08 GMT

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

Re: COUNTIFS Functionality in DAX

v-sihou-msft — Tue, 21 Feb 2017 09:13:54 GMT

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,

Re: COUNTIFS Functionality in DAX

Anonymous — Thu, 20 Jul 2017 10:18:13 GMT

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!

Re: COUNTIFS Functionality in DAX

vollmers — Mon, 23 Jul 2018 22:16:12 GMT

shucks.. not quite for what i want..

I have 2 columns

mark - blue

suzy - red

suzy - blue

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

suzy - red - 1

suzy - blue - 1

john - yellow - 2

mary - blue - 1

Re: COUNTIFS Functionality in DAX

vollmers — Mon, 30 Jul 2018 17:46:52 GMT

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

INPUT:

NAME - COLOR

mark - red

mark - blue

john - red

john - blue

john - yellow

OUTPUT:

NAME - COLOR - COUNT

mark - red - 2

mark - blue - 1

john - red - 1

john - blue - 1

john - yellow - 1

Re: COUNTIFS Functionality in DAX

Sean — Mon, 30 Jul 2018 18:35:38 GMT

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:

Re: COUNTIFS Functionality in DAX

vollmers — Mon, 30 Jul 2018 20:07:41 GMT

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

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

Re: COUNTIFS Functionality in DAX

emoncada — Fri, 07 Dec 2018 20:55:33 GMT

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?

Re: COUNTIFS Functionality in DAX

Anonymous — Mon, 17 Aug 2020 06:39:56 GMT

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

Re: COUNTIFS Functionality in DAX

GlassShark1 — Tue, 16 Mar 2021 13:26:06 GMT

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 🙂

Re: COUNTIFS Functionality in DAX

Katja262373 — Thu, 16 Nov 2023 18:08:13 GMT

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-percentage-calculation/m-p/3537654#M135935