Forum Discussion
Filtering columns in a Matrix
- 2 years ago
Hi Necrosis1968, I assume you've built your matrix by dragging and dropping columns and then changing calcualtion to "Count (Distinct)". Is it correct?
If so, you're using "implicit" measures as commented by Greg_Deckler. "Implicit" means that PowerBI makes a calculation for you and you don't need to write any formula.
The reason you get 1 when filtering "Resolved" is because when apply it, your table is filtered only to 2 rows (Resolved = Yes) and each "Problem Type" has only 1 "Unique ID" at that moment.
What you might want to do is to "ignore" filter on "Resolved". To do so, you need to use "explicit" measures. The following measure will help you to achieve it:Count Unique ID = CALCULATE( //changes calculation context DISTINCTCOUNT( 'Table'[ UniqueID ] ), //perform this operation ALL( 'Table'[ Resolved] ) //but only when this rule is applied (nevertheless you see it a row below it applies before ) )Now, the filter "Resolved" is ignored so you get a proper count.
You can use the similar logic to calcualte resolved, unresolved cases as well as find the resolution percentage:Count Resolved = CALCULATE( DISTINCTCOUNT( 'Table'[ UniqueID ] ), 'Table'[ Resolved] = "Yes" ) Count Not Resolved = CALCULATE( DISTINCTCOUNT( 'Table'[ UniqueID ] ), 'Table'[ Resolved] = "No" ) % Resolved = DIVIDE( [Count Resolved], [Count Unique ID] )
Here is the final output:I'd suggest you to complete this course on YouTube before you going deeper in using PowerBI, it will help you a lot! Introducing DAX 101 videos (youtube.com)
Good luck 🙂
Also tried the % code but I get the error:
The syntax for 'Count' is incorrect. (DAX( CALCULATE( DISTINCTCOUNT( 'Table'[UniqueID] ), 'Table'[Resolved] = "Yes" )Count Not Resolved = CALCULATE( DISTINCTCOUNT( 'Table'[UniqueID] ), 'Table'[Resolved] = "No" )% Resolved = DIVIDE( [Count Resolved], [Count Unique ID] )))
I have changed my table name to Table so it all matches yours.
Not that the filtering works though.
Silly really as in excel you use a pivot table and add Resolved as a filter. That's what I'm trying to replicate.
Hi Necrosis1968, the block of my previous reply contains 3 separate measures 😉
Count Resolved =
CALCULATE(
DISTINCTCOUNT( 'Table'[ UniqueID ] ),
'Table'[ Resolved] = "Yes"
)
Count Not Resolved =
CALCULATE(
DISTINCTCOUNT( 'Table'[ UniqueID ] ),
'Table'[ Resolved] = "No"
)
% Resolved = DIVIDE( [Count Resolved], [Count Unique ID] ) - Sergii242 years agoSuper User
P.S. some column names in my code have spaces in their names. Make sure to adjust them to your data model 😉
- Necrosis19682 years agoRegular Visitor
Thank you Sergii24 I now have it fully working. I have removed the columns and put the measures in and it works exactly as you have shown it!!!
- Necrosis19682 years agoRegular Visitor
Wow! Thank you that explains it!