Forum Discussion

jojo54's avatar
jojo54
Frequent Visitor
7 years ago
Solved

Start and end time

Hi there, pretty new to Power BI.  I'm trying to replicate something I do in Excel in Power BI, mainly because excel can't handle the amount of data that's being processed.   I'm trying to figure o...
Need Help
  • Geradav's avatar
    Geradav
    7 years ago

    Hi jojo54 

     

    See if the following can fix it for what you need

    Number of clients 2 =
VAR vMinVal =
    MIN ( TimeTable[Value] )
VAR vMaxVal =
    MAX ( TimeTable[Value] )
VAR vNumberOfClients =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( ClientsTbl ),
            ClientsTbl[Entry Time] <= vMinVal
                && ClientsTbl[Exit Time] >= vMaxVal
        )
    )
RETURN
    vNumberOfClients

     

    Whenever I have more time I could think of something better.

Geradav's avatar
Geradav
Icon for Responsive Resident rankResponsive Resident
7 years ago

Hi jojo54 

 

Here is an alternative using DAX

Number of clients =
VAR vMinVal =
    MIN ( TimeTable[Value] )
VAR vMaxVal =
    MAX ( TimeTable[Value] )
VAR vClientEntry =
    CALCULATE (
        COUNTROWS ( ClientsTbl ),
        TimeTable[Value] <= vMinVal,
        ALL ( TimeTable )
    )
VAR vClientExit =
    CALCULATE (
        COUNTROWS ( ClientsTbl ),
        TimeTable[Value] >= vMaxVal,
        ALL ( TimeTable ),
        USERELATIONSHIP ( ClientsTbl[Exit Time], TimeTable[Value] )
    )
RETURN
    MIN ( vClientExit, vClientEntry )

Create a time table, then create a relationship between Start Time and the time value, and a second one between Exit Time and the time value. One will be an active relationship, while the second will be inactive.

Like this:

 

Then you can use that measure in your visualization. Time value in X axis and the measure in the Value area

And you can also make use of the same DAX code in the data model of Excel (Power Pivot)

 

Let us know what works for you

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Geradav ,

     

    Nice approach using dax! Is there a way to make the interval evey 15mins instead of every 5mins?

    • Geradav's avatar
      Geradav
      Icon for Responsive Resident rankResponsive Resident
      7 years ago

      Hi mussaenda 

       

      I used a table function to generate a time series with an increment of 5 minutes, so yes, it can easily be changed to 15 minutes.

      TimeTable =
GENERATESERIES ( TIME ( 5, 0, 0 ), TIME ( 13, 0, 0 ), TIME ( 0, 15, 0 ) )
  • jojo54's avatar
    jojo54
    Frequent Visitor
    7 years ago

    Geradav 

     

    Thanks for this solution!  I like that I can actually write this down to work out what it's doing!

     

    But I'm finding an issue with the below: (updated table)

    NameEntry TimeExit Time
    Person 16:009:00
    Person 27:008:30
    Person 38:159:15
    Person 46:4510:00
    Person 57:157:45

     

    If I were to look at 8am, according to the DAX query,

    vcliententry = 4

    vclientexit = 4

    So query will return 4 being min (4,4)

     

    However there's actually only 3 people who's at the cafe (Person 1,2,4)

     

    I tried writing this on paper and I've worked out that this mis-calculation occurs when there are people on the list who have exited prior to the TimeValue AND there are people who not yet entered into the cafe at the TimeValue.

     

    Any ideas on how to fix this?

     

    Thanks!

     

    • Geradav's avatar
      Geradav
      Icon for Responsive Resident rankResponsive Resident
      7 years ago

      Hi jojo54 

       

      See if the following can fix it for what you need

      Number of clients 2 =
VAR vMinVal =
    MIN ( TimeTable[Value] )
VAR vMaxVal =
    MAX ( TimeTable[Value] )
VAR vNumberOfClients =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( ClientsTbl ),
            ClientsTbl[Entry Time] <= vMinVal
                && ClientsTbl[Exit Time] >= vMaxVal
        )
    )
RETURN
    vNumberOfClients

       

      Whenever I have more time I could think of something better.

      • jojo54's avatar
        jojo54
        Frequent Visitor
        7 years ago

        Geradav thank you!! That works perfectly!