Processing a subset of data and creating subtotals

[kriscii]

New to PowerBI and trying to see if it is straightforward to process the data set we have. It is a set of telemetry data that looks like this:

There is a session start (tid=1) and session end (tid=12, 13, 14 or 15). I want to create a dashboards showing number of sessions with durations and the type of session end (tid=12, 13, 14 or 15). Within those sessions the other actions (tid up to 25 types) that happen and subtotals of each of those. Each day is a single json object represented by the table above which has a number of of other fields not shown.

Before I spent a lot of time on this I wanted to see if this was going to be straightforward and the techniques that should be used. Happy to go and then do the research and start buildng this. To add some context there will be telemetry for a number of devices and will then create larger overviews at a later date.

Thanks for any advice.

Chris

[v-shex-msft · Accepted solution]

HI @kriscii,

You can try to use below measure to calculate duration between tid '1':

Duration=
VAR currTID =
    MAX ( Table[tid] )
VAR currDate =
    MAX ( Table[created] )
VAR nextDate =
    //nex start tid
    CALCULATE (
        MIN ( Table[created] ),
        FILTER ( ALLSELECTED ( Table ), [tid] = 1 && [created] > currDate )
    )
VAR endDate =
    CALCULATE (
        MIN ( Table[created] ),
        FILTER ( ALLSELECTED ( Table ), [tid] <> 1 && [created] < nextDate )
    )
RETURN
    IF (
        currTID = 1
            && nextDate <> BLANK (),
        DATEDIFF ( currDate, endDate, SECOND )
    )

For total sessions between specific items, you can modify formula and use countrows to get specific result.

Session count=
VAR currTID =
    MAX ( Table[tid] )
VAR currDate =
    MAX ( Table[created] )
VAR nextDate =
    //nex start tid
    CALCULATE (
        MIN ( Table[created] ),
        FILTER ( ALLSELECTED ( Table ), [tid] = 1 && [created] > currDate )
    )
VAR endDate =
    CALCULATE (
        MIN ( Table[created] ),
        FILTER ( ALLSELECTED ( Table ), [tid] <> 1 && [created] < nextDate )
    )
RETURN
    IF (
        currTID = 1,
        COUNTROWS (
            FILTER ( ALLSELECTED ( Table ), [created] > currDate && [created] < endDate )
        )
    )

Regards,

Xiaoxin Sheng

[kriscii]

Hi @v-shex-msft

Thank you for the formulas, I've been travelling and haven't had a chance to try them. I'll take a look but that seems to be the sort of thing I needed. 

Many thanks

Chris