Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Solved! Go to 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?
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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |