Want to exclude lines of data with 0 or no value in any measure

[aali2688]

Hi experts,

In the below screenshot, I am struggling with a challenge where power bi shows products having no data or zero values in the specified time period. I only want those products has data in any measure for any dimension in case. 

Highlighted products are the example which should be excluded.

Any thoughts?

[v-xuxinyi-msft]

Hi @aali2688 

Are the columns of values shown in your screenshot all measures? Or only Total Sale is measure?

Below is my test for creating a measure to calculate Total Sale, hope it helps.

Ensure that “Show items with no data” option shown are unchecked

Result:

Please feel free to let me know if I've misunderstood you.

Best Regards,
Yulia Xu

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

[aali2688]

Thank you for your reply. I have checked the the "Show item with no data" is already unchecked and the approach in Dax using the MAX function is not feasable in my case as I have date wise transactional data for each product in power query window and using this approach I can only get the max value in any of the date against the given product and store.

hope I can explain my situation.

[v-xuxinyi-msft]

Hi @aali2688 

I modified the source data structure for testing, in which Net Sales and Closing Stock are columns.

Then I created a measure as follows.

Measure = 
 VAR _a = CALCULATE(
    SUM('Table'[Closing Stock]),
    ALLEXCEPT('Table', 'Table'[ProductID])
 )
  VAR _b = CALCULATE(
    SUM('Table'[Net Sales]),
    ALLEXCEPT('Table', 'Table'[ProductID])
 )
 RETURN
_a + _b

Put the measure into the visual-level filters, set up show items when the value is not 0.

Best Regards,
Yulia Xu

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

[aali2688]

Thank yo for your help. 

Your approach is beneficial to control this kind of situation but in power bi desktop or service. here I have users available using "Analyze in Excel" where cant use this in filters. 

What we can do in this case?

Is there a way that we can modify our existing sales and stock measures not to show values with blank or zero in any view or report.?