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.

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

Hi jojo54 ,

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEyszIwAFKWICpWBy5lBBQzh0hZWBmjSBmDxaC6DE2RpUzABpqYAilDAzQTTcEmGkIooJLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Entry Time" = _t, #"Exit Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Entry Time", type time}, {"Exit Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Times([Entry Time], 20, #duration(0, 0, 15, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type time}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Exit Time] < [Custom] then "Hide" else "Show"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "Show"))
in
    #"Filtered Rows"

From the result you posted, The person 3 must also have count in 8:00 time because 8:00 is his entry time, right?

File is here

wynhopkins's avatar
wynhopkins
Icon for Most Valuable Professional rankMost Valuable Professional
7 years ago

Nice approach mussaenda ,   why use the 20 in List.Times?

 

Also, jojo54 ,  given this code will work in Excel I'd be interested to know what performance issues your having that you're trying PowerBI.   Is it the 32 Bit Excel version that's limiting you?

 

Wyn

 

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

    Hi wynhopkins,

     

    Thank you fo the compliment. I just used 20 because I assumed that a person stays in a cafe max like 5 hours.

    20 can be changed, jojo54 .  depends on your need. :)

     

    Have a nice day!

  • jojo54's avatar
    jojo54
    Frequent Visitor
    7 years ago

    I have ~3m lines of data so excel is extremely limiting! I'm looking at it across every day, across a few years