Forum Discussion
COUNTIFS Functionality in DAX
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
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
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,
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!
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
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
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:
you are awesome. i'm going to keep bugging you. congrats :)
I have 100s of thousands of rows and thousands of unique combinations.
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?
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_id1COUNTROWS ( FILTER ( Table, Table[fault_code] = fault_code1 && Table[equipment_id] = equipment_id1Dear 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!
