Forum Discussion

bipowerbix's avatar
bipowerbix
Icon for Helper I rankHelper I
3 years ago
Solved

Getting count of re-occurring values within the same table for dates instead of date/time

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/...
  • bipowerbix's avatar
    bipowerbix
    3 years ago

    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 AMSam
    5/1/2023 6:00:00 PMSam
    5/1/2023 8:00:00 AMTim
    5/2/2023 9:00:00 AMJake
    5/2/2023 11:00:00 AMJake
    5/3/2023 12:00:00 AMAhmed
    5/3/2023 7:00:00 AMWoo
    5/3/2023 4:00:00 AMSam
    5/4/2023 5:00:00 PMJake
    5/5/2023 2:00:00 PMOscar
    5/5/2023 6:00:00 AMJames
    5/6/2023 9:00:00 AMJake
    5/6/2023 10:00:00 AMSam
    5/7/2023 5:00:00 AMTim
    5/8/2023 7:00:00 AMSam
    5/8/2023 8:00:00 AMAhmed
    5/9/2023 6:00:00 AMSam
    5/9/2023 11:00:00 AMOscar
    5/9/2023 3:00:00 AMSam
    5/10/2023 1:00:00 AMTim
    5/11/2023 9:00:00 AMAhmed
    5/11/2023 8:00:00 AMSam
    5/12/2023 10:00:00 AMOscar

     

     

    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))

     

bipowerbix's avatar
bipowerbix
Icon for Helper I rankHelper I
3 years ago

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 AMSam
5/1/2023 6:00:00 PMSam
5/1/2023 8:00:00 AMTim
5/2/2023 9:00:00 AMJake
5/2/2023 11:00:00 AMJake
5/3/2023 12:00:00 AMAhmed
5/3/2023 7:00:00 AMWoo
5/3/2023 4:00:00 AMSam
5/4/2023 5:00:00 PMJake
5/5/2023 2:00:00 PMOscar
5/5/2023 6:00:00 AMJames
5/6/2023 9:00:00 AMJake
5/6/2023 10:00:00 AMSam
5/7/2023 5:00:00 AMTim
5/8/2023 7:00:00 AMSam
5/8/2023 8:00:00 AMAhmed
5/9/2023 6:00:00 AMSam
5/9/2023 11:00:00 AMOscar
5/9/2023 3:00:00 AMSam
5/10/2023 1:00:00 AMTim
5/11/2023 9:00:00 AMAhmed
5/11/2023 8:00:00 AMSam
5/12/2023 10:00:00 AMOscar

 

 

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))