如何在数据具有日期/时间时计算过去7天内出现次数的频率。

huangapple go评论64阅读模式
英文:

How to count frequency of occurrences in the last 7 days when data has date/time

问题

以下是样本数据集,包含2列,数据和姓名。

问题是创建一个度量,该度量将计算姓名出现次数大于2的数量。

步骤1 是找到每天至少出现一次但没有时间戳的姓名:

步骤2 是从步骤1的输出中找到出现次数超过2次的姓名。

步骤3 是从步骤2中获取姓名的不同计数:

期望输出 = 2

不确定如何在DAX中实现这一点。

英文:

Here is the sample dataset, having 2 columns, Data, and Name
如何在数据具有日期/时间时计算过去7天内出现次数的频率。

The problem is to create a measure that is going to count the number of name occurrences having a count greater than 2

Step 1 is to find the Names that show up at least once a day without a timestamp

如何在数据具有日期/时间时计算过去7天内出现次数的频率。

Step 2 is to find names that have occurrences of more than 2 from the output got of Step 1:

如何在数据具有日期/时间时计算过去7天内出现次数的频率。

Step 3: is to get the distinct count of names from Step 2:

Expected Output = 2

Not sure how to do this in DAX.

Note: I m looking for a measure that gives the Output = 2 and not the steps, the steps are for understanding purposes/ or to provide clarity

答案1

得分: 1

First write a measure to count the names

创建一个度量值来计算名称数量

Measure = CALCULATE(COUNT(Data[Name]), FILTER(ALLEXCEPT(data, Data[Name]), Data[Date] <= DATEVALUE("2023-05-07")))

Measure = CALCULATE(COUNT(Data[Name]), FILTER(ALLEXCEPT(data, Data[Name]), Data[Date] &lt;= DATEVALUE(&quot;2023-05-07&quot;)))

Then use that measure to count names more than 2

然后使用该度量值来计算超过2个的名称数量

Measure 2 = CALCULATE(DISTINCTCOUNT(Data[Name]), FILTER(data, [Measure] > 2))

Measure 2 = CALCULATE(DISTINCTCOUNT(Data[Name]), FILTER(data, [Measure] &gt; 2))
英文:

First write a measure to count the names

Measure = CALCULATE(COUNT(Data[Name]), FILTER(ALLEXCEPT(data, Data[Name]), Data[Date] &lt;= DATEVALUE(&quot;2023-05-07&quot;)))

Then use that measure to count names more than 2

Measure 2 = CALCULATE(DISTINCTCOUNT(Data[Name]), FILTER(data, [Measure] &gt; 2))

如何在数据具有日期/时间时计算过去7天内出现次数的频率。

答案2

得分: 0

如果您将日期和时间列拆分为单独的列或在事实表中创建它们,那么使用FILTER和COUNTROWS()以及可能的RELATEDTABLE()将变得很简单。

英文:

If you either split your date and time column into separate columns or create them in your fact table, then this is trivial using FILTER and COUNTROWS() and maybe RELATEDTABLE().

答案3

得分: 0

以下是已经翻译好的部分:

测试更新的列表:

我基本上做了这个:

Measure =
var base =
SUMMARIZE(data,Data[Name],"Count",count(Data[Date]))
return calculate( countrows(filter(base,[Count] >2)))

这会输出:Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3)

然后,为了获取最近7天内频繁出现的名字的计数,我使用以下度量标准:

Measure 2 =
var last_7 = now() - 7
return calculate( [Measure], filter(Data, Data[Date] >= last_7))

结果如下所示:检查在最近7天内出现超过2次的计数(从2023年5月5日开始),结果为2,因为计数>2的是Sam(5)和Oscar(3)。

英文:

Testing for updated list:

如何在数据具有日期/时间时计算过去7天内出现次数的频率。

I basically did this:

Measure = 
var base = 
SUMMARIZE(data,Data[Name],&quot;Count&quot;,count(Data[Date]))
return calculate( countrows(filter(base,[Count] &gt;2)))

which gives the output: Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3)

如何在数据具有日期/时间时计算过去7天内出现次数的频率。

Then to get the count of frequent names in the last 7 days I use the below measure:

Measure 2 = 
var last_7 = now() - 7
return calculate( [Measure], filter(Data, Data[Date] &gt;= last_7))

The result is shown below: Checks for count more than 2 times in the last 7 days (that is starting 5/5/2023), The result is 2, because the counts>2 are Sam(5) and Oscar(3).

如何在数据具有日期/时间时计算过去7天内出现次数的频率。

huangapple
  • 本文由 发表于 2023年5月11日 09:22:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/76223535.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定