Forum Discussion

tjd's avatar
tjd
Impactful Individual
6 years ago
Solved

Filter/Hide Matrix row If Column Value Has One or More Zero

DAX experts - I have a matrix visual consisting of Users (rows - from Dim-Users) who respond in various categories (columns which come from a filtered subset of Fact-Elements[CatName], Cat A, Cat B, ...
Need Help
  • tjd's avatar
    tjd
    6 years ago

    Solved.  I created the following measures:

    1. # Categories = DISTINCTCOUNT('FACT-ELEMENTS'[CatName]) - calculates number of categories each User has a value > 0.
    2. # Categories (Max) = CALCULATE(DISTINCTCOUNT('FACT-ELEMENTS'[CatName]), ALLSELECTED('USERNAMES'[User_ID])) - calculates the maximum number of categories across all users, i.e., the maximum number of categories I could have if all had a value >0

    3. # Categories (Show Row) = IF([# Categories]=[# Categories (Max)], 1,0) - used to filter the matrix visual.  If 1, user has at least a value >0 in each category and should be shown.  Anything else is suppressed.

RickWald's avatar
RickWald
Frequent Visitor
4 years ago

Awessome fix - I used this expression on a matrix with multiple tables pulled together in my data model with only a few modifications.

Worked great!

askpbiuser's avatar
askpbiuser
Helper I
2 years ago

Hi, I have a similar problem, however I want to hide columns if it has even one zero value. So just imagine if Category B had no 0 value then Category A column needs to be hidden. How can I achieve this?