Forum Discussion

jjr333's avatar
jjr333
Icon for Advocate I rankAdvocate I
7 years ago
Solved

Count instances where Aggregated Sum is zero

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] ) ...
Need Help
  • jjr333's avatar
    jjr333
    7 years ago

    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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
jjr333
Icon for Advocate I rankAdvocate I
7 years ago

Hi v-juanli-msft ,

Yes, case closed. ;-)

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

Thanks,

J.