Count stores with sales > zero measure calculating very slowly

[Mainer04401]

I'm using the following DAX to calculate the number of stores with sales greater than zero and it's causing any visuals with this measure to load VERY slowly.  Is there a more efficient way to calculate this?

calculate(
distinctcount([Store ID]),
filter('Data Table',[Sales]>0)

)

I have 180,000 stores and six time periods, each time period appended (not loaded) into one table, so it's just over 1 million rows that PowerBI is working through.  I'm grouping the stores by dimensions in a seperate lookup table.  So, for example, I may be showing the store count in each of the 50 states in a matrix table.  

[az38 · Accepted solution]

Hi @Mainer04401 

I see the only way to make statement like (notice, without FILTER())

calculate(
distinctcount([Store ID]),
[Sales]>0

)

 Try to filter out during ETL process or in Power Query editor mode

[Klabonty]

Well it sure makes sense when you show me!

Thanks!!

[Mainer04401]

@az38Thanks but that formula gives me the error: A function "CALCULATE" has been used in a True/False expression that is used as a table filter expression.  This is not allowed.  

[az38]

ok, @Mainer04401 

what is [Sales]? is it a measure or calculated column? if yes, show the statement

[Mainer04401]

@az38It is a measure with the statement: sum([Sales Volume].  Sales Volume is the native column in the data.  

I modified the store count formula, replacing that measure with the native sales volume column (without filter) and it appears to be accurately counting the stores with positive volume and doing it MUCH quicker!  I had used the filter function because I thought I needed it to iterate through the table, otherwise for example I thought it would count every store in a state if the entire state had volume greater than zero (if the state was in the visual) , rather than only counting individual stores with positive sales.   But I guess when I'm referencing the column, it puts it in row level context, so it works.  Thanks!