Multiple count if

[cottrera]

Hi 

I need to use dax to carry out a count if function. I have a property safety table and need to count the number of properties where the 'Cert has expired' column says 'Expired' however I also need to ingore (subtract) and properties where the 'Status' column says 'Works Completed' 

I have attached an example of the table for which the result I expect is 6

Ref	Status	Has certificate expired
377	Works Completed	Expired
212		Expired
145		Expired
238		Expired
249		Expired
98	Works Completed	In date
286	Works Completed	In date
106	Works Completed	Expired
115	Job Scheduled	In date
261		In date
179		In date
346		In date
254	Works Completed	Expired
209		Expired
76	Job Scheduled	Expired
315	Job Scheduled	In date
226		In date
80	Works Completed	In date
50	Works Completed	In date

thank you

[amitchandak · Accepted solution]

Try

calculate(count(table1[Ref]),filter(table1,table1[Has certificate expired]='Expired' && table1[Status]!='Works Completed'))

[Anonymous]

Hi,

You could use something like below.

Table_Expr:= SUMMARIZE(FILTER(Table, Table[Status]<>"Works Completed"), Table[Has certificate expired], "Count", COUNT(Table[Has certificate expired]))

Best Regards,
Vignesh M

If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue 🙂