Forum Discussion
Filter/Hide Matrix row If Column Value Has One or More Zero
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 >03. # 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.
See if this helps. If you want to show the details of CATA and CATB you will need a two step approach.
First, use this measure. This will return BLANK() if there are any zeros in the CATA/CATB column.
Conditional Total =
IF(
MAX('Table'[CATA]) = 0 || MAX('Table'[CATB]) = 0,
BLANK(),
SUMX(
'Table',
'Table'[CATA] + 'Table'[CATB]
)
)
Then filter the matrix to hide any records that have a blank in the [Conditional Total] measure. Below is an image of two Matrix visuals. The one on the right is filtered.
My PBIX file is here if you want to look at it.
edhans: Thank you for your attempt at this. I think that what everyone is missing is that Cat A, Cat B, etc. are NOT columns. They are categories within the column Category (i.e., table Fact-Response[Category]="Cat A" Fact-Response[Category]="Cat B"). This is how they wound up in a matrix as column headers. The matrix plots User[UserID] (row), Fact-Response[Category] (multiple columns due to multiple categories) and the value is Count(Fact-Response[ResponseDisplay]).
I'll give your solution a try but I don't think is going to work as it appears to be predicated on CATA being a table column "MAX(Table[CATA])=0)" which it is not. What I think I need is a conditional of the form:
RemoveRow = IF (CALCULATE(COUNT('Fact-Response[ResponseDisplay), Fact-Response[Category]="CATA" = 0) || CALCULATE(COUNT('Fact-Response[ResponseDisplay), Fact-Response[Category]="CATB" = 0)), 1, 0) but that doesn't seem to work correctly. Sometimes it does some filtering but not completely.
Can you please provide data and a sample file via instructions here?
The reason we think it is a column is it looks like a column in your screenshot, so we are kinda guessing here. Categories are columns, you just may be grouping them. I'm still guessing here....
Thanks.
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!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?
