cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RicFischer
Helper I
Helper I

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

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:

 

RicFischer_7-1623360362819.png

 

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:

 

RicFischer_2-1623360136951.png

 

RicFischer_3-1623360179753.png

 

RicFischer_4-1623360206829.png

 

And my list of tables:

 

RicFischer_5-1623360259344.png

 

Finally, this is how I set up the Matrix:

 

RicFischer_6-1623360328503.png

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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)

061401.jpg

061402.jpg

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

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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)

061401.jpg

061402.jpg

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

View solution in original post

That worked nicely. Thanks!

RicFischer
Helper I
Helper I

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors