Forum Discussion

arieldorfman's avatar
arieldorfman
New Member
6 years ago
Solved

concurrent users

Hi all

im new at PBI, and i have this situation

we need to have how many concurrent users are loged in to our platform every minute

i have this table, in xls 

 

UsuarioStartTimeEndTimeDuration
A20/03/20 08:04:4320/03/20 10:04:43N/A
B17/03/20 15:06:2517/03/20 17:06:25N/A
B20/03/20 2:15:0420/03/20 2:16:0800:01:04
C20/03/20 2:30:1620/03/20 6:26:1603:56:01
B20/03/20 6:48:1420/03/20 6:49:1900:01:06
D20/03/20 6:56:3620/03/20 7:07:3700:11:01

 

how is the best way to have this metrics?

thanks in advance!

Ariel

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please take following steps:

    1)Create a new query in Query editor:

    let
    Source = List.DateTimes(#datetime(2020,1,1,03,00,00),100,#duration(0,0,1,0)) 
in
    Source

    2)After Apply&Close this query, try to create two columns in original data table:

    Start = DATE(YEAR('Table'[StartTime]),MONTH('Table'[StartTime]),DAY('Table'[StartTime]))+TIME(HOUR('Table'[StartTime]),MINUTE('Table'[StartTime]),0)
    End = DATE(YEAR('Table'[EndTime]),MONTH('Table'[EndTime]),DAY('Table'[EndTime]))+TIME(HOUR('Table'[EndTime]),MINUTE('Table'[EndTime]),0)

    3)Try this measure:

    Count of User per minute =
SUMX (
    DISTINCT ( Query1[Query1] ),
    CALCULATE (
        COUNTAX (
            FILTER (
                'Table',
                'Table'[Start] <= MAX ( Query1[Query1] )
                    && 'Table'[End] >= MAX ( Query1[Query1] )
            ),
            'Table'[Usuario]
        )
    )
)

    4)The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please take following steps:

    1)Create a new query in Query editor:

    let
    Source = List.DateTimes(#datetime(2020,1,1,03,00,00),100,#duration(0,0,1,0)) 
in
    Source

    2)After Apply&Close this query, try to create two columns in original data table:

    Start = DATE(YEAR('Table'[StartTime]),MONTH('Table'[StartTime]),DAY('Table'[StartTime]))+TIME(HOUR('Table'[StartTime]),MINUTE('Table'[StartTime]),0)
    End = DATE(YEAR('Table'[EndTime]),MONTH('Table'[EndTime]),DAY('Table'[EndTime]))+TIME(HOUR('Table'[EndTime]),MINUTE('Table'[EndTime]),0)

    3)Try this measure:

    Count of User per minute =
SUMX (
    DISTINCT ( Query1[Query1] ),
    CALCULATE (
        COUNTAX (
            FILTER (
                'Table',
                'Table'[Start] <= MAX ( Query1[Query1] )
                    && 'Table'[End] >= MAX ( Query1[Query1] )
            ),
            'Table'[Usuario]
        )
    )
)

    4)The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto