cancel
Showing results for
Did you mean:
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

Accepted Solutions
Moderator

Re: COUNTIFS Functionality in DAX

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

9 REPLIES 9
Super Contributor

Re: COUNTIFS Functionality in DAX

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!

Good Luck!

Frequent Visitor

Re: COUNTIFS Functionality in DAX

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

Moderator

Re: COUNTIFS Functionality in DAX

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

Frequent Visitor

Re: COUNTIFS Functionality in DAX

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!

Frequent Visitor

Re: COUNTIFS Functionality in DAX

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

Frequent Visitor

Re: COUNTIFS Functionality in DAX

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

Super Contributor

Re: COUNTIFS Functionality in DAX

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!

Frequent Visitor

Re: COUNTIFS Functionality in DAX

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

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

Visitor

Re: COUNTIFS Functionality in DAX

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?

Announcements

Community Highlights

Find out what's new in the Power BI Community!

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 318 members 3,254 guests
Recent signins: