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...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Please format the data type of [Date] column in Calendar table to Date/Time, which is the same as data[Time of Connection] and data[Time of Disconnection].

 

Regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi v-yulgu-msft

 

they are all formatted as Date/Time

 

Format: (dd/MM/yy HH:mm)

 

should the relationship be between the Time_of_Connection and the calendar date data table?

 

Sample Data:

 

DataTable1

 

UsernameTime_of_connectionTime_of_disconnection
JBEAN14/7/2018 11:0214/7/2018 11:05
BEEMER14/7/2018 11:0214/7/2018 11:06
HLEFT14/7/2018 11:0114/7/2018 11:08
JUP14/7/2018 11:00

14/7/2018 11:05

JDOWN14/7/2018 10:5514/7/2018 10:58

 

 

So on a visualisation at the minute 14/7/2018:11:03 should show the number of users connected: 4

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

    Hi Anonymous,

     

    There is no need to create a relationship between DataTable1 and calendar table.

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      hi @ 

       

      without a relationship it doesnt calculate anything at all. with a relationship it doesnt calculater correctly:(

       

      this is the atcual formula im using;

       

      Count Measure =
      CALCULATE (
          COUNTA(data[callingPartyUnicodeLoginUserID] ),
          FILTER (
              Data,
              SELECTEDVALUE ( 'Calendar'[Date] ) >= data[Time of Connection]
                  && SELECTEDVALUE ('Calendar'[Date]) <= Data[Time of Disconnection]
          )
      )