Countif with multiple criteria - DAX

[TSantoro99]

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!

[Anonymous · Accepted solution]

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.

[TSantoro99]

@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.   Many, many, thanks!