Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |