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
