0

I have a tabular cube containing data from our chat system. I need to make a measure for "Concurrent Chats".

I have a start datetime and an end datetime column for each chat session and need to count all those where either startdate or enddate is in a given time interval or where startdate is before and enddate is after the interval.

Any good suggestions?

Thx a lot

NicolajB
  • 275
  • 1
  • 6
  • 16

1 Answers1

1

The simple way to approach this is probably with a 'disconnected slicer' - in this case you would create a single column table full of datetimes that is not related to your chats table.

You then write the measure to pick up the selection from your Chat table accordingly. Something like this will do the job:

=CALCULATE (
COUNTROWS ( chats ),
FILTER (
    ALL ( chats ),
    chats[Start] <= MIN ( dateTime[DateTime] )
        && chats[End] >= MAX ( dateTime[DateTime] )
         )
       )

This would give you all chats in progress at the highest and lowest datetimes you select.

nb datetime[DateTime] is your disconnected slicer.

Jacob
  • 3,437
  • 3
  • 18
  • 31
  • Jacob, what do you do for living? Anytime I want to answer something, you've already done so! :-) – Petr Havlik Jan 22 '15 at 08:44
  • Sorry mate, will let you take the next one :-) – Jacob Jan 22 '15 at 09:04
  • Thx a lot - I didn't thought of making the disconnected slicer. I still have one problem though. the "ALL(chats)" in the filter takes away the option of filtering the chats on other attributes. The chats belong to different departments and countries which I would like to apply filters for. Is there a way to add the filter for the disconnected slicer but still use the other filters? – NicolajB Jan 22 '15 at 09:05
  • No problem. There is another possible solution that is more complicated using USERELATIONSHIP() you could look into. – Jacob Jan 22 '15 at 09:06
  • Hi Jacob.... You are so FAST here :-) I edited my comment because I submitted it too fast..... :-) – NicolajB Jan 22 '15 at 09:08
  • Does `ALL( chats [start], chats [end])` fix the problem? – Jacob Jan 22 '15 at 09:12