Forum Discussion
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, etc.). The values are the #Responses=CountRows(Fact-Elements[ResponseDisplay]). How do I filter/hide those rows that have one or more zero responses (the red boxes)? Thanks in advance for your help.
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.
10 Replies
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?
You can put a visual filter.
If data is pivoted first unpivot it
https://radacad.com/pivot-and-unpivot-with-power-bi , cat a and Catb goes to response
Then you have a formula like
Calculate(count(user_id),[response]>0)
Amitchandak - not exactly sure what that formula is trying to do, but it gives me a type mismatch: User_id is text and can't use it. All I want to do is remove rows that do not have all values > 0. One would think that would be pretty simple. Works easy enough when I have one column but not when there are multiple categories from the same column.
Can you share sample data and sample output.
Appreciate your Kudos.Mark me with @
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.
