Function to count one login value per day

[jsnyce]

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

[samdthompson]

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

[jsnyce]

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 · Accepted solution]

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

[jsnyce]

Now I understand what you meant before, I apologize! Both of those are great options.  I will have to try both and see which works better.  Thank you very much for your help and quick responses!!!! 

[samdthompson]

No worries mate, its often to get so consumed by the model that its hard to find a way. 

[jsnyce]

you aren't kidding... I've been staring at this thing for a few days trying to think of a solution.  It's one of those things that seems so simple but just impossible to figure out at the time.  Always nice to get insight from others out there!