date slicer for grouped login data

[Laurence-]

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!

[AllisonKennedy]

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?

[Laurence-]

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!

[v-diye-msft]

Hi @Laurence- 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[AllisonKennedy]

Yes, 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. 🙂