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

[tjd]

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.

[tjd · Accepted solution]

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.

[edhans]

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.

[RickWald]

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]

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? 

[tjd]

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.

[edhans]

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.

[amitchandak]

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)

[tjd]

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.

[amitchandak]

Can you share sample data and sample output.

Appreciate your Kudos.Mark me with @

[Anonymous]

Ok then first convert that cat A into int.
Add that cat A to visual level filter and set it as greater than 0.
If cat a is column then simplelu unselect 0 from visual level filter for cat A

If measure convert it it int and add to visual level filter.


Thanks
Pravin