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

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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:

DateName
5/1/2023 10:00Sam
5/1/2023 18:00Sam
5/1/2023 8:00Tim
5/2/2023 9:00Jake
5/2/2023 11:00Jake
5/3/2023 0:00Ahmed
5/3/2023 7:00Woo
5/3/2023 4:00Sam
5/4/2023 17:00Jake
5/5/2023 14:00Oscar
5/5/2023 6:00James
5/6/2023 9:00Jake
5/6/2023 10:00Sam
5/7/2023 5:00Tim
5/8/2023 5:00Tim
5/9/2023 5:00Tim
5/9/2023 5:00Tim

it worked like:

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

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 )

 

 

 

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi bipowerbix 

    what is the data type of data[date]?