exclude data when one of the tables contain 0

[Anonymous]

Hi Guys,

can anyone help with my question below:

i have two reports from 2 sources contain same products, however i do not want the pbi dashboard page/visuals to show the data when either one of the report contains 0. for example, both report 1 and 2 are from 2 different sources, and with same product ,if apple and pineapplle are showing 0 in table 2 then apple and pineapple will not show data in dashboard, so my visuals will show banana only. what dax can i apply for this situation for all pages and visuals in the dashboard

[amitchandak · Accepted solution]

@Anonymous , If i can assume report 1 and report 2 are table and sum of value is 0

Value1 is measure from report 1 and value 2 is measure from report 2

One way is put report level filter  value1 <> 0 and value2 <> 0

or create measure like 

value1 new =

if( [value1] = 0 && [Value2] =0, blank(), [Value1])

value2 new =

if( [value1] = 0 && [Value2] =0, blank(), [Value2])

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I think you can try to build a measure to filter your visual as well.

My Sample:

Measure:

Filter = 
IF(SUM(Source1[Report1])<>0&&SUM('Source 2'[Report 2])<>0,1,0)

Build a visual ,add this measure into visual level filter field and set it to show items when value =1.

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak · Accepted solution]

@Anonymous , If i can assume report 1 and report 2 are table and sum of value is 0

Value1 is measure from report 1 and value 2 is measure from report 2

One way is put report level filter  value1 <> 0 and value2 <> 0

or create measure like 

value1 new =

if( [value1] = 0 && [Value2] =0, blank(), [Value1])

value2 new =

if( [value1] = 0 && [Value2] =0, blank(), [Value2])