Create new table (or measure?) to fill out all values with 0 when no document found

[RicFischer]

My new project.

I need to create a matrix that lists only people with any missing document.

Here is the visual that needs to be fixed:

Tony (row 2) has a blank column that's filled with a 4 in the Matrix visual. Alternatively if I don't do Count or Count Distinct for the Values of the report, it shows "Certificate 1" everywhere it shows "4". Because of the way the data is retrieved, things are awkard and I need help cleaning things up so I can get better results. I'd like to use a simple true/false or something similar that I can use Conditional Formatting to put a green checkmark if they have the document or a red X if they don't. Finally, I'd like to filter out everyone that all four document types, because I don't need to worry about them.

Here's my sample file: Sample file

And screenshots of my tables:

And my list of tables:

Finally, this is how I set up the Matrix:

[v-jingzhang · Accepted solution]

Hi @RicFischer 

You could create below measures. Use Count Measure as a filter to show items with value is less than 4. And use Has this file? measure to set conditional formatting on value to show only icons.

Count Measure = DISTINCTCOUNT(DocListTable[DocumentType])

Has this file? = IF([Count Measure]>0,1,0)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[RicFischer]

That worked nicely. Thanks!

[RicFischer]

I found a solution on my own. It's possibly inelegant. Feedback is welcome.

I created a calculated column that uses consecutive if/lookup formulas:

Document Count =
    IF(LEN(LOOKUPVALUE(DocListTable[DocumentType],DocListTable[Name],EmployeeListTable[EmployeeName],DocListTable[DocumentType],"Certificate 1")) > 1, 1, 0)
    + IF(LEN(LOOKUPVALUE(DocListTable[DocumentType],DocListTable[Name],EmployeeListTable[EmployeeName],DocListTable[DocumentType],"Certificate 2")) > 1, 1, 0)
    + IF(LEN(LOOKUPVALUE(DocListTable[DocumentType],DocListTable[Name],EmployeeListTable[EmployeeName],DocListTable[DocumentType],"Certificate 3")) > 1, 1, 0)
    + IF(LEN(LOOKUPVALUE(DocListTable[DocumentType],DocListTable[Name],EmployeeListTable[EmployeeName],DocListTable[DocumentType],"Document 1")) > 1, 1, 0)

Using this, if the employee has all 4 documents, the Document Count value will be 4. If they are missing any document, it will be less than 4.

Perhaps there was a more elegant way to do this, but it was the best method I could think of so far.

Now I use Document Count as a filter where if the value is less than 4 then it shows only employees that are missing at least one document.