Summarize projects without audit date

[Anonymous]

Hello,

I have 2 Data Sources. (Data samples below)

ProjectsDB

ProjectID

A630BDP

433SRMA

A630POB

433PPC

433PED

433BUP

A325WLO

433FRI

ProjectAuditsDB

ProjectAuditID	ProjectID	AuditDate
433PPC_BA03	433PPC	13/06/2016
433PED_BA07	433PED	14/06/2016
433SRMA_BA06	433SRMA	08/06/2016
B891FMM_BA02	B891FMM	07/06/2016
A630BDP_BA10	A630BDP	25/05/2016
433GFO_BA01	433GFO	23/05/2016
A630POB_BA02	A630POB	16/05/2016

Some Projects have been audited and some have not. The latter, therefore, have no Audit Date.

I wanted to create a Summarize Table with all the Project IDs that were not audited using the following expression.

ProjectAuditsDB_NOT AUDITED = SUMMARIZE(ProjectsDB,ProjectsDB[ProjectID],"NOT AUDITED", CALCULATE(COUNTAX(ProjectAuditsDB,ProjectAuditsDB[AuditDate]),filter(ProjectAuditsDB,ProjectAuditsDB[AuditDate]="")))

But it is generating the following error

"DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

I tried using

filter(ProjectAuditsDB,ProjectAuditsDB[AuditDate]=null)))

But it is not accepted

"Operator or expression 'NULL' is not supported in this context."

This one does not generate an error but it returns the wrong project list, and I cannot figure out why either.

ProjectAuditsDB_NOT AUDITED = SUMMARIZE(ProjectsDB,ProjectsDB[ProjectID],"NOT AUDITED", CALCULATE(COUNTAX(ProjectAuditsDB,ProjectAuditsDB[AuditDate]),filter(ProjectAuditsDB,ProjectAuditsDB[AuditDate]=BLANK())))

How can I do it?

Thank you for your help.

J

[Vvelarde · Accepted solution]

HI @Anonymous

Try with this:

ProjectAuditsDB_NOT AUDITED = SUMMARIZE(Filter(ProjectsDB;COUNTROWS(RELATEDTABLE(ProjectAuditsDB))=0);ProjectsDB[ProjectID])

[Anonymous]

YES!

You did it again @Vvelarde!

Thank you.

J