Hide row if all the measure values=0

[ak77]

Hi all,

I have a matrix table with 20 columns calculated as measures .

i want to hide the rows which are zero for all 20 column values . is there any common way of handling this case in DAX? please help  

[Ritaf1983 · Accepted solution]

Hi @ak77 
You can create Flag measure :

test = if ([measure1]=0&& [measure 2]=0&& [measure 2 ]=0,..........[measure 20] =0,1,0)
Use this measure as a filter for your matrix.
I created an example with other numbers just to show you the logic:

You can download the pbix From this link 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @ak77 

Unfortunately, since your condition uses an 'and' relationship and you want to filter out rows only when all measures are blank, I don't believe there is a simpler solution. According to the dataset, the issue may not be related to size but rather to other measures.

Please take a look at the linked video; you might find something that can help

https://www.youtube.com/watch?v=b2b-z5Iv-cM

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ahmedx]

Flag =
VAR _AllMeasures = [measure 1]+[measure 2]+ [measure 3] ..... [measure 20]
RETURN
if( _AllMeasures > 0 ,1)
-----------

this will help you filter the matrix in visual filter level just select 1

[ak77]

@Ahmedx thanks for reply.. i tried the above solution as mentioned. there are some constraints.

1.All my measure has '--' character and addition of measures breaks while summing up. 

2. its a large dataset ( 3 million + records) and resulting in resource exceed errors) in published version? 

[Ahmedx]

everything in your report is amazing!
how are these measures and how can you look at 3 mellions in the matrix and how do you have records
and there are not zeros but --
you show us the screenshot

[ak77]

@Ahmedx , pls see the screenshot.. 3 million in total . we have a 10 year annual return measure where 10 years data sum is calcualted.. 

[Ahmedx · Accepted solution]

try this

IF ( MAX ('table'[Asset I Sub-Class]) = "ExcessReturns",0,1)

[ak77]

@Ahmedx , Sorry.. i didnt get .. how will IF ( MAX ('table'[Asset I Sub-Class]) = "ExcessReturns",0,1) help in hiding the row if all values are zero. Please let me know

[ak77]

@Ritaf1983 , this works for smaller datasets.. i have a 3 million data and on trying this it gives exceeded memory error. is there a way to calculate if all columns =0 in a dynamic way instead of having all columns in IF condition. Please check

[Ritaf1983 · Accepted solution]

Hi @ak77 
You can create Flag measure :

test = if ([measure1]=0&& [measure 2]=0&& [measure 2 ]=0,..........[measure 20] =0,1,0)
Use this measure as a filter for your matrix.
I created an example with other numbers just to show you the logic:

You can download the pbix From this link 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @ak77 

Unfortunately, since your condition uses an 'and' relationship and you want to filter out rows only when all measures are blank, I don't believe there is a simpler solution. According to the dataset, the issue may not be related to size but rather to other measures.

Please take a look at the linked video; you might find something that can help

https://www.youtube.com/watch?v=b2b-z5Iv-cM

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly