Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

PowerBI maximum Concurrent Callers - Stats DAX

Hi please can you help, i have data that has a call start date and time

 

Time_of_connection

Time_of_Disconnection

 

in Date/ Time format

 

I also have a User_ID field;

 

i need to calculate the number (Count) the maximum number of users on a call at any point in time (Daily)

 

How can i calculate this?

 

 

 

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    Iam assuming you have a date table and relationship is set properly. With that assumption, the below one should work, please try.

     

    CALCULATE ( COUNT(Table1[User_ID]), FILTER( Table1, SELECTEDVALUE(Date_Table[DateTime]) >=Table1[Time_of_connection] && SELECTEDVALUE(DateTime) >=Table1[Time_of_Disconnection]) )

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      Anonymous wrote:

      Hi Anonymous

       

      Iam assuming you have a date table and relationship is set properly. With that assumption, the below one should work, please try.

       

      CALCULATE ( COUNT(Table1[User_ID]), FILTER( Table1, SELECTEDVALUE(Date_Table[DateTime]) >=Table1[Time_of_connection] && SELECTEDVALUE(DateTime) >=Table1[Time_of_Disconnection]) )

       

      Thanks

      Raj

      Sorry for the spell mistake. The highlighted part should be <=.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        @rajendran

         

        Hi my calendar table looks like this: i now need to do this by Minute/Hour

         

        Calendar =
        SELECTCOLUMNS (
        CROSSJOIN (
        CALENDAR (MIN(data[Time of Connection]), MAX(data[Time of Disconnection].[Date]) ),
        DATATABLE (
        "Hour", DATETIME,
        {
        {"00:01"},
        {"00:02"},
        {"00:03"},
        {"00:04"},
        {"00:05"},
        {"00:06"},
        {"00:07"},
        {"00:08"},
        {"00:09"},
        {"00:10"},
        {"00:11"},
        {"00:12"},
        {"00:13"},
        {"00:14"},
        {"00:15"},
        {"00:16"},
        {"00:17"},
        {"00:18"},
        {"00:19"},
        {"00:20"},
        {"00:21"},
        {"00:22"},
        {"00:23"},
        {"00:24"},

        and so on until 23:59
        }
        )
        ),
        "Date", [Date] + [Minute]
        )

         

        what fields do i need to create the relationship between the Date and table? i currently have it set to Time_of_Connection and and the date table.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Rajendran's suggestion worked on my test. But need some little modification:

    Count Measure =
CALCULATE (
    COUNT ( Table1[User_ID] ),
    FILTER (
        Table1,
        SELECTEDVALUE ( Date_Table[DateTime] ) >= Table1[Time_of_connection]
            && SELECTEDVALUE ( DateTime ) <= Table1[Time_of_Disconnection]
    )
)

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-yulgu-msft

       

      thanks for that, what format should my date table be in for this to work?

       

      and if i need to do a visualisation that showed the number of concurrent calls taking place on an hour by hour basis from 9am until 6pm each day how could i do this?

       

      regards