Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count rows if between two time slots

Dear all,   I have the following issue which I think should be relatively easy to solve, but I have not found a solution yet. Currently I have below table in excel but I would like to get the same ...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    You can modify the formula like this:

    Column =
VAR __Start = [Timestart]
VAR __End = [Time + 10 Minutes]
VAR _count =
    COUNTROWS (
        FILTER ( ALL ( 'table' ), [Timestamp] >= __Start && [Timestamp] <= __End )
    )
RETURN
    IF ( ISBLANK ( _count ), 0, _count )

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous - Should be something like:

Column = 
  VAR __Start = [Timestart]
  VAR __End = [Time + 10 Minutes]
RETURN
  COUNTROWS([Timestamp]>=__Start && [Timestamp]<=__End)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dear Greg_Deckler 

 

Thanks for your quick reply! Your solution translates as following in my powerBI file:

 

Column =
VAR __Start = 'KPI-Export-2020-09'[Start]
VAR __End = 'KPI-Export-2020-09'[End]
RETURN
COUNTROWS('KPI-Export-2020-09'[Date/time]'>=__Start && 'KPI-Export-2020-09'[Date/time]<=__End)
 
However, I get the following error:
Syntax for '>=__Start&& is not correct
 
Do you perhaps know how I should re-write the syntax? I've tried a couple of things but not succesful unfortunately.
 
Best regards!
 
  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi Anonymous ,

    You can modify the formula like this:

    Column =
VAR __Start = [Timestart]
VAR __End = [Time + 10 Minutes]
VAR _count =
    COUNTROWS (
        FILTER ( ALL ( 'table' ), [Timestamp] >= __Start && [Timestamp] <= __End )
    )
RETURN
    IF ( ISBLANK ( _count ), 0, _count )

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.