Forum Discussion
Countif with multiple criteria - DAX
Hello,
I am looking for some assistance in writing a DAX formula which will act as a countif with multiple criteria. I have the excel version here with some simplified data for an example:
I am looking to add a column to my data table that will count the number of times a unique combination of ID and Week appear.
My true data set is all in one table (StudentRelatedServicesDetail) and I would need to count number of times that a student's service (RelatedServiceID) is scheduled during a given week (Either: ServiceDate or YearAndWeek).
Any assistance that can be provided would be much appreciated!
Thank you!
Hi TSantoro99,
You can create a column by concatenating the columns id and week with M code in your Query Editor, like this:
#"Added Custom" = Table.AddColumn(#"Previous step", "Concatenated", each [id]&"_"&[Week])
and then using DAX create a column with the count of occurrences of the concatenated values, using this formula:Count_occurence =
COUNTX (
FILTER ( Table; EARLIER ( Table[Concatenated] ) = Table[Concatenated] );
Table[Concatenated]
)Regards.
2 Replies
Hi TSantoro99,
You can create a column by concatenating the columns id and week with M code in your Query Editor, like this:
#"Added Custom" = Table.AddColumn(#"Previous step", "Concatenated", each [id]&"_"&[Week])
and then using DAX create a column with the count of occurrences of the concatenated values, using this formula:Count_occurence =
COUNTX (
FILTER ( Table; EARLIER ( Table[Concatenated] ) = Table[Concatenated] );
Table[Concatenated]
)Regards.
Anonymous - Thank you so much Felipe! You have saved me many hours of hair-pulling and frustration trying to get that to work! It looks excellent and is exactly what I needed. :smileyvery-happy: Many, many, thanks!
