Removing Zero valued NA rows which are measures

[Saipreethi]

Hi Everyone,

I have a table with Clients and sub category of Countries with all 15 measure columns. I want to remove countries which have zero values in all columns. Can anyone help with logic. I have implemented by taking every measure as Variable and applied "AND" condition between them and kept that column in table and applied visual level filter. But I am missing something in the above. Need help!

[v-yueyunzh-msft · Accepted solution]

Hi, @Saipreethi 

According to your descriptionAccording to your description, you want to not display data where all columns are 0. Right?

Here are the steps you can follow：

(1)This is my test data:

(2) To simulate the measure you said, on my column I put 3 measures that I created:

(3)We can create a measure : “isDisplay”

isDisplay = IF( [Actuals2]=0 && [Budget2]=0 && [Detial2]=0 , -1,1)

(4)We can put the [isDisplay] in the “Filter on this visual” and configure it , and then click “Apply Fillter”:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft · Accepted solution]

Hi, @Saipreethi 

According to your descriptionAccording to your description, you want to not display data where all columns are 0. Right?

Here are the steps you can follow：

(1)This is my test data:

(2) To simulate the measure you said, on my column I put 3 measures that I created:

(3)We can create a measure : “isDisplay”

isDisplay = IF( [Actuals2]=0 && [Budget2]=0 && [Detial2]=0 , -1,1)

(4)We can put the [isDisplay] in the “Filter on this visual” and configure it , and then click “Apply Fillter”:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[Saipreethi]

Hi, 
It is not going to the sub category level of country when I apply the above condition. You can check the screenshot after applying the condition also im getting 0's,Nulls and NA. Firstly It has to go to client and maximize it for country level and then go to country level and then the condition has to be applied. Could you please me with this.

[v-yueyunzh-msft · Accepted solution]

Hi, @Saipreethi 

You want to hide the value with sublevel 0. Right?

 (1)This is my test data:

(2)We can use the ISINSCOPR() function to judge the level:

isDisplay = IF( ISINSCOPE('Sheet2'[item2])&&[Actuals2]=0&&[Budget2]=0&&[Detial2]=0,-1,1)

If my understanding is inconsistent with your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[tamerj1]

Hi @Saipreethi 

you can create a filter measure and place it in the filter pane and select "Is blank" then apply the filter. 
Filter Measure = IF ( [Measure1] = 0 && [Measure2] && [Measure3] && .........., 1 )