Forum Discussion
Function to count one login value per day
Hello all,
I’ve used this forum many times to find answers to issues I’ve had just from reading the solutions to posts from other users. This is the first time I’ve actually created a submission, so I hope you can help!
I manage a CRM system for my organization and I am attempting to build an adoption summary. I have a table of logins but unfortunately depending how many times a user opens or closes the web application it will cache as a login. I’m trying to create a measure to only count one login for a user for a given day. So in theory this column would count the first login for a given day and ignore the others. Any thoughts how I can create this?
Thanks in advance for any help you can provide!
Below is a screenshot of the Audit logs I reference above.
Access Table
The distinctcount will give you the result you are looking for as a measure rather than a field. You could bring the user count through onto a bar chart with date on the xaxis and then change the summarisation on the values field to a distinct count. That would also work. If you are certain you want the count on the individual row level you would need to do a =CALCULATE(DISTINCTCOUNT(Table[USERID]),ALLEXCEPT(Table,Table[Date],Table,[UserID])) that would give you a field that when bought through would give you the samne result, but would show a 1 on each of the rows if you looked at the table.
// If this is a solution please mark as such
6 Replies
samdthompsonMemorable Member
Hello, maybe try this measure
=DISTINCTCOUNT(Table[USERID])
you can then slice and dice by day or what ever you need.
// If this is a solution please mark as such
Thank you for your reply!
Unfortunately, this doesn't seem to be what I was looking for. Effectively, what I would like to do, is add a column onto my systemusers table that will count how many days a user has logged in, and only count one login for a given day. I've been trying to see if I can come up with a fuction to do this, but I am not having much luck. Not sure if this is even possible or not...
- samdthompson
The distinctcount will give you the result you are looking for as a measure rather than a field. You could bring the user count through onto a bar chart with date on the xaxis and then change the summarisation on the values field to a distinct count. That would also work. If you are certain you want the count on the individual row level you would need to do a =CALCULATE(DISTINCTCOUNT(Table[USERID]),ALLEXCEPT(Table,Table[Date],Table,[UserID])) that would give you a field that when bought through would give you the samne result, but would show a 1 on each of the rows if you looked at the table.
// If this is a solution please mark as such
