Filtering columns in a Matrix

[Necrosis1968]

Hi, I'm a Power BI Newbie so sorry for the simplicity of my question. I've looked on the forums but can't find anything that I can get to work.

I have a matrix based on a table. One column is calculated using DAX. A data Sample is below:

Problem Type       UniqueID     Resolved

App                             A1               Yes

App                             A2               No

OS                               A3               No

OS                               A4               Yes

In my matrix I have

Problem Type          Count of Unique ID          Count of Resolved

App                                       2                                 2

OS                                         2                                 2

I'd like to filter resolved = No, or Resolved = Yes, but when I do, all columns go to 1. The count of Unique ID would need to remain at 2. I tried adding a slicer but that affects all columns. I also tried clicking on count of resolved and trying to add a slicer there but it selects both resolved and unique ID whenever I try that (presumably becuase the Resolved status is calculated?).

This is so I can then work on the next problem which will be the % resolved in another column, but that's for another time!

Thanks in advance!

[Sergii24 · Accepted solution]

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 🙂

[Necrosis1968]

I was using count, rather than count distinct on Resolved. Otherwise, when we use more rows in the sample data, we would end up with 1 as the count as Resolved can only be Yes or No. 

Thus a larger data set would look like:

Problem Type       UniqueID     Resolved

App                             A1               Yes

App                             A2               No

App                             A6               No

App                             A7               No

OS                               A3               No

OS                               A4               Yes

OS                               A8               Yes

With the desired output:

Problem Type          Count of Unique ID          Count of Resolved

App                                       4                                 1

OS                                         3                                 2

I tried the code you left, but it still doen't filter the Resolved column. I must be doing something fundamentally wrong.

Thanks for helping on the % which I'll attempt once the filter is working.

I'll start the YouTube link you suggested now! Thanks so much for the recommendation.

[Necrosis1968]

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.

[Necrosis1968]

Update! I have managed to get some success. I had to put the measure in the matrix values and remove the original unique ID I was using (hey I said I was a newb). The output now shows below. Not as neat as @Sergii24 but getting there. Just can't get the syntax right of the % calculation in @Sergii24 second piece of code. Thanks so much for your help so far both!

[Sergii24]

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] ) 

[Necrosis1968]

Wow! Thank you that explains it!

[Sergii24]

P.S. some column names in my code have spaces in their names. Make sure to adjust them to your data model 😉

[Necrosis1968]

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!!!

[Greg_Deckler]

@Necrosis1968 Hard to know for sure but you probably need to use an explicit measure instead of an implicit measure like this:

Count of Unique ID Measure = 
  VAR __ProblemType = MAX('Table'[Problem Type])
  VAR __Result = COUNTROWS( FILTER( ALL( 'Table' ), [Problem Type] = __ProblemType ) )
RETURN
  __Result

[Necrosis1968]

Sadly it didn't work. I tried adding it but it doesn't filter when clicking Yes or No on the slicer.

Is it possible to ignore the slicer and get the following output if it's easier?

Problem Type          Count of Unique ID          Count of Resolved=No                 Count of Resolved=Yes

App                                       2                                 1                                                            1

OS                                         2                                 1                                                            1

Would prefer a filter but if it's easier then that would work too as I'm a newb.