sql-server - How to count number of emails and average of emails sent by hour?

Question

I'm working on reports with Power BI. I want to get for each hour the number of emails sent. I did the query in SQL Server but I'm having a difficulty translating it to DAX to create a measure.Also I want to get the average number of emails sent by hour.

My query is like below :

 select datepart(hour,CreatedDate), count(MailId) from [DW PI].[dbo].[Email Fact] 
      group by datepart(hour,CreatedDate)
      order by count(MailId) desc 

标签： sql-servertsqlpowerbi

Answer 1

您需要在表上创建一个额外的列：

Hour = HOUR(Email Fact[CreatedDate])

现在，您将此列与 MailId 一起添加到您的表视觉对象中。

最后一点：你想要一个 MailId 的计数，而不是 Sum。对你来说，这是 MailId 的计数