Forum Discussion
date slicer for grouped login data
Hi, I'm relatively new to using PowerBI. I've got some data that is related through a number of tables, and want to create a basic table to report on logins (stored as a date/time timestamp). The basic relationship is as below, for each login the bb_logins query holds a row with the studentid and date/time of their login. iiq_identities holds the master user account details, with other queries providing more information about the user (student).
Of course not all studentid's necessarily appear in bb_logins (if they haven't logged in at all!), it's a many-to-one relationship where there are multiple lines for individual logins for a user.
Listed here is sample data from the bb_logins query :
I am wanting to create a report to report on first / last and count of logins between specific durations (e.g. this studentid logged in 5 times, between Sept 7th 2020, and Sept 13th 2020. To try and tackle this, i've made a calculated table
SDate = CALENDAR (DATE(2020,9,1), DATE(2020,12,31)) with a "between" slicer on it and measures to get the Selected Start and Selected End date.
What i need to do is group the bb_logins data by studentid, to produce the following table (using min, max and count). Below i have done this in the powerquery editor, to create a new query, without any slicer for the date/time -
Bearing in mind that steps in powerquery run at refresh, I believe that I need to achieve the same result using DAX instead?
How would I best go about it providing the end user the ability to use the date slicer to view this information from the source table
Many thanks for any help!
4 Replies
- AllisonKennedy
Community Champion
Since you only want to see the logins between the selected dates, you should be able to do this using a relationship between the SDate table and your bb_logins table. Only thing is you'll need to add a column in Power Query to the bb_logins table that gets just the Date component of the login time, so you can use that to relate to your SDate table.
Then put studentid in rows in a matrix. First Login in values, Last login in values, count logins in values. Hope that makes sense?Hi Allison, thank you! that's moving further in the right direction, I've created the calculated date column as suggested on the bb_logins and the date slicer filters that fine. However, I need to somehow create calculate the aggregations on this dataset dynamically following this.
I had done the grouping and max / min / count through a separate query before, which I've tried to relate in to the bb_logins table, but presumably this is the wrong approach? as I need it to re-calculate dynamically based on the filter (e.g. show first / last login between the newly selected range).
A further part of this is that if there are 0 between the range, then I want to see that (primarily that's what I want to know!). I've merged in the iiq_identities studentid query to pull in all studentid's (above), but of course the date slicer will filter out any null values for loginTimestamp / date.
Thanks again!
- AllisonKennedyYes, you are right that anything in Power Query will not update based on slicer selection.
I think you should have the groupings already, but if you want to see the null/blank values, then use the 'Show Items with no data' option on StudentId. Still put the same matrix configuration I suggested in the post above.
If that is not what you meant, please draw a picture or excel of what your desired result compared to sample data is so I can see where I'm going wrong in understanding your requirements. 🙂
