英文:
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
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
Step 2 is to find names that have occurrences of more than 2 from the output got of Step 1:
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] <= DATEVALUE("2023-05-07")))
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] > 2))
英文:
First write a measure to count the names
Measure = CALCULATE(COUNT(Data[Name]), FILTER(ALLEXCEPT(data, Data[Name]), Data[Date] <= DATEVALUE("2023-05-07")))
Then use that measure to count names more than 2
Measure 2 = CALCULATE(DISTINCTCOUNT(Data[Name]), FILTER(data, [Measure] > 2))
答案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:
I basically did this:
Measure =
var base =
SUMMARIZE(data,Data[Name],"Count",count(Data[Date]))
return calculate( countrows(filter(base,[Count] >2)))
which gives the output: Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3)
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] >= 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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论