Count instances where Aggregated Sum is zero

[jjr333]

I have a dimDate table and a related fact table. 

The fact table contains gas readings per day per hour.

How do I calculate the number of days per year where the average day measure ( [PM10 Ave] ) exceeds the value of 50, using DAX?

[PM10 Threshold count] returns the number of hourly readings which exceed 50.

Cheers,

J.

PM10 Threshold count =  // Year norm: max. 35 days/year where PM10 avg/day >= 50 microgram/m3
                        CALCULATE(COUNTA(factSanSeb[PM10]); FILTER(factSanSeb; [PM10 Ave]>=50))

[v-juanli-msft]

Hi @jjr333 

If  dimDate table have a datetime columns with format (year/month/day hh:mm:ss)

Then i create calculated columns in this table

year = [datetime].[Year]

date = [datetime].[Date]

Then create measures in this table

sum = CALCULATE(SUM(Sheet3[pm10]),ALLEXCEPT('DATETIME TABLE','DATETIME TABLE'[date]))

count_hours_perday = CALCULATE(DISTINCTCOUNT('DATETIME TABLE'[datetime]),ALLEXCEPT('DATETIME TABLE','DATETIME TABLE'[date]))

average = [sum]/[count_hours_perday]

days = CALCULATE(DISTINCTCOUNT('DATETIME TABLE'[date]),FILTER(ALLEXCEPT('DATETIME TABLE','DATETIME TABLE'[year]),[average]>=50))

Please refer to my pbix to see more details.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jjr333 · Accepted solution]

Hi Maggie,

Thank you.

In the mean time I have used a different approach: GROUPBY() and a virtual table.

DaysExceeding50 = 
VAR varPM10DailyAve =
GROUPBY(Sheet3;'DATETIME TABLE'[date];"varcolAvePM10";AVERAGEX(CURRENTGROUP();Sheet3[pm10]))
RETURN COUNTROWS(FILTER(varPM10DailyAve;[varcolAvePM10]>=50))

Kind regards,

Julian
PS: how does one attach a pbix file?

[v-juanli-msft]

Hi @jjr333 

When replying, do you see the "choose file" option?

If not, you could upload file to OneDrive and share the file link here.

In addition, your formula does also slove your problem, right?

Can we consider this case as closed?

Best Regards
Maggie

[jjr333]

Hi @v-juanli-msft ,

Yes, case closed. 😉

And, no I do not see the "Choose Files" button.

Thanks,

J.