Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Consider the table with 2 columns: Date and Name, Date is in Date Time Format.
Date | Name |
5/1/2023 10:00 | Sam |
5/1/2023 18:00 | Sam |
5/1/2023 8:00 | Tim |
5/2/2023 9:00 | Jake |
5/2/2023 11:00 | Jake |
5/3/2023 0:00 | Ahmed |
5/3/2023 7:00 | Woo |
5/3/2023 4:00 | Sam |
5/4/2023 17:00 | Jake |
5/5/2023 14:00 | Oscar |
5/5/2023 6:00 | James |
5/6/2023 9:00 | Jake |
5/6/2023 10:00 | Sam |
5/7/2023 5:00 | Tim |
Want to get the total Count of Names that appear more than 2 times in the last 7 days? (May 7 to May 1) in this example dataset.
If the name appears twice on the same day, then it should be considered as 1 (Image of the resulting Dataset shown below),
Note: This image shows the result dataset, it's not the output. The Output is 2
Expected Output: 2 (Since Sam and Jake appear more than two times in the 7-day timeframe)
Solved! Go to Solution.
Solved it using the below codes
Measure =
var base = SUMMARIZE(data,Data[Name],"Count",count(Data[Date]))
return calculate( countrows(filter(base,[Count] >2)))
and
Measure 2 =
var last_7 = now() - 7
return calculate( [Measure], filter(Data, Data[Date] >= last_7))
Basically, grouping Names with day counts in step 1, which is defined by "Measure", then filtering out the results of "Measure" for the last 7 days in "Measure 2"
below is test dataset:
DateName
5/1/2023 10:00:00 AM | Sam |
5/1/2023 6:00:00 PM | Sam |
5/1/2023 8:00:00 AM | Tim |
5/2/2023 9:00:00 AM | Jake |
5/2/2023 11:00:00 AM | Jake |
5/3/2023 12:00:00 AM | Ahmed |
5/3/2023 7:00:00 AM | Woo |
5/3/2023 4:00:00 AM | Sam |
5/4/2023 5:00:00 PM | Jake |
5/5/2023 2:00:00 PM | Oscar |
5/5/2023 6:00:00 AM | James |
5/6/2023 9:00:00 AM | Jake |
5/6/2023 10:00:00 AM | Sam |
5/7/2023 5:00:00 AM | Tim |
5/8/2023 7:00:00 AM | Sam |
5/8/2023 8:00:00 AM | Ahmed |
5/9/2023 6:00:00 AM | Sam |
5/9/2023 11:00:00 AM | Oscar |
5/9/2023 3:00:00 AM | Sam |
5/10/2023 1:00:00 AM | Tim |
5/11/2023 9:00:00 AM | Ahmed |
5/11/2023 8:00:00 AM | Sam |
5/12/2023 10:00:00 AM | Oscar |
Result: 5 Users with more than 2 count (Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3))
and 2 in the last 7 days since 5/5/23 (Sam(5) and Oscar(3))
Solved it using the below codes
Measure =
var base = SUMMARIZE(data,Data[Name],"Count",count(Data[Date]))
return calculate( countrows(filter(base,[Count] >2)))
and
Measure 2 =
var last_7 = now() - 7
return calculate( [Measure], filter(Data, Data[Date] >= last_7))
Basically, grouping Names with day counts in step 1, which is defined by "Measure", then filtering out the results of "Measure" for the last 7 days in "Measure 2"
below is test dataset:
DateName
5/1/2023 10:00:00 AM | Sam |
5/1/2023 6:00:00 PM | Sam |
5/1/2023 8:00:00 AM | Tim |
5/2/2023 9:00:00 AM | Jake |
5/2/2023 11:00:00 AM | Jake |
5/3/2023 12:00:00 AM | Ahmed |
5/3/2023 7:00:00 AM | Woo |
5/3/2023 4:00:00 AM | Sam |
5/4/2023 5:00:00 PM | Jake |
5/5/2023 2:00:00 PM | Oscar |
5/5/2023 6:00:00 AM | James |
5/6/2023 9:00:00 AM | Jake |
5/6/2023 10:00:00 AM | Sam |
5/7/2023 5:00:00 AM | Tim |
5/8/2023 7:00:00 AM | Sam |
5/8/2023 8:00:00 AM | Ahmed |
5/9/2023 6:00:00 AM | Sam |
5/9/2023 11:00:00 AM | Oscar |
5/9/2023 3:00:00 AM | Sam |
5/10/2023 1:00:00 AM | Tim |
5/11/2023 9:00:00 AM | Ahmed |
5/11/2023 8:00:00 AM | Sam |
5/12/2023 10:00:00 AM | Oscar |
Result: 5 Users with more than 2 count (Sam(8), Tim(3), Ahmed(3), Jake(4), Oscar(3))
and 2 in the last 7 days since 5/5/23 (Sam(5) and Oscar(3))
hi @bipowerbix
try to plot a measure like:
Measure =
VAR _list =
ADDCOLUMNS(
VALUES(data[Name]),
"Count",
CALCULATE(
COUNTROWS(
FILTER(
data,
data[Date]>=DATE(2023,5,1)
&&data[Date]<=DATE(2023,5,7)
)
)
)
)
RETURN
COUNTROWS(
FILTER(
_list,
[Count]>2
)
)
verified with enriched data like:
Date | Name |
5/1/2023 10:00 | Sam |
5/1/2023 18:00 | Sam |
5/1/2023 8:00 | Tim |
5/2/2023 9:00 | Jake |
5/2/2023 11:00 | Jake |
5/3/2023 0:00 | Ahmed |
5/3/2023 7:00 | Woo |
5/3/2023 4:00 | Sam |
5/4/2023 17:00 | Jake |
5/5/2023 14:00 | Oscar |
5/5/2023 6:00 | James |
5/6/2023 9:00 | Jake |
5/6/2023 10:00 | Sam |
5/7/2023 5:00 | Tim |
5/8/2023 5:00 | Tim |
5/9/2023 5:00 | Tim |
5/9/2023 5:00 | Tim |
it worked like:
I wanted to get it for the last 7 days, I have used the below-listed code instead od 5/1/2023 and 5/7/2023, I get an error saying
"DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values"
data[date] >= (date(year(now()),month(now()),day(now())) -7 )
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |