Forum Discussion
Impactful IndividualFilter/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.
Community ChampionSee 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.
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!
askpbiuserHelper I
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?