Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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.
That worked nicely. Thanks!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |