Struggling with DAX Measures in a Push Dataset

[Anonymous]

Hi all, 

after a solid couple of days wrestling with Push Datasets, I need to ask for some help please......

I'm trying to create a realtime dashboard of the office where we can see the number of concurrent users we have as well as what the max concurrency we had over the last 3 hours.  I would also like a line chart for that period so we can see what the minute-to-minute movement is, as loads are a bit spikey at the moment.  However, i'm really struggling!

I have no problems creating this with a normal imported dataset, but I want this to be a Push Dataset so that we get near realtime updates. This means I can't use Calculated Columns as I would normally.

This is the dashbaord I want to achieve (but with the push dataset)

My data is coming straight into the REST API and it's very lightweight, but I can't do any upstream manipulation.

Session ID - Unique identifier for each user session

SessionStart - timestamp for the start of the session

SessionEnd - timestamp for end of session

ApplicationId - the application being run

Here's a very small snaphot of the data I recieve:

So what I have been trying to achieve is a Row-Level counter. Each time a new start event comes the value on the row in incremented by one and then similar, when an end event comes in, it decreases by one. In my mind, this has two advantages:

1) I can show a trended view with a line chart

2) I can then apply a Max() measure to it to find the peak concurrency.

Like I said, i'm smaking my head against the wall as I don't have calculated columns to work with, so hoping there is another way of doing this.

Final piece of context of where I am right now: I'm using the current DAX measure to keep a track on my concurrency, but it's a single value. I can't see any trended data nor can I see a max as it's a column-level agregate:

ActiveSessions =

VAR SampleStart = UTCNOW()-TIME(3,0,0)

RETURN

CALCULATE(

DISTINCTCOUNT(RealTimeData[sessionid]),

FILTER(ALL(RealTimeData),

NOT(ISBLANK(RealTimeData[applicationid])) &&

DATEVALUE(RealTimeData[startsession]) > SampleStart

)

)

-

CALCULATE(

DISTINCTCOUNT(RealTimeData[sessionid]),

FILTER(ALL(RealTimeData),

NOT(ISBLANK(RealTimeData[applicationid])) &&

DATEVALUE(RealTimeData[endsession]) > SampleStart

)

)

Thanks soooo much in advance for you help / ideas.

Casp.

[v-piga-msft]

Hi @Anonymous ,

I'm a little confused about your scenario.

Which connector do you use in power bi desktop?

If it is convenient, could you show your sample data in text-tabular format in addition to (or instead of)  screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Best Regards,

Cherry

[Anonymous]

Morning Cherry,

Thanks a million for following up.

I've attached a Pbix file with a sample of data from a test we were running yesterday. It's not production traffic, but the scenario is that you'll see a rapid ramp-up and then ramp-down of sessions. (two tests in quick succession)

https://www.dropbox.com/s/fktouty70xt7u5p/Realtime%20Sessions%20v1.pbix?dl=0

In terms of connector - then I configured the dataset in the online portal as a streaming dataset with historic data enabled.

I moved to powerbi BI desktop and connected to that dataset so that I could build the measures and then push the resulting report back up into my workspace. I then pinned each of the elements to my live dashabord, so they update in realtime.

As mentioned, I would like to be able to create a line chart for concurrent users

I would also like to be able to determine my peak concurrency 

Hope that makes sense?

Thanks

Casp.