Trouble with DAX calculating the number of distinct log ins

[et123]

I have a table called 'View Data' that holds unique log ins to a report portal and the reports the user viewed; I want to count the views but not those that were in the same hour.  For example in the view below you can see this one user has 3 entries for the same time and date but this should only be counted once.

Any ideas on how to write a measure that will do this?

Thanks

[Anonymous · Accepted solution]

Hi @et123 ,
Thanks for divyed and rajendraongole1 reply.

Here is my sample data

For the error in rajendraongole1  expression, you can change the code to

UniqueViewsPerHour = 
VAR UserHourGroups =
    ADDCOLUMNS(
        'View Data',
        "HourKey",
        CONCATENATE(
            FORMAT('View Data'[DateCreated], "YYYY-MM-DD HH"),
            'View Data'[SignIn]
        )
    )
RETURN
COUNTROWS(SUMMARIZE(UserHourGroups, [HourKey], 'View Data'[EmailAddress]))

Or you can create another measure

DistinctRowsMeasure = 
COUNTROWS(
    SUMMARIZE(
        'View Data', 
        'View Data'[DateCreated], 
        'View Data'[SignIn], 
        'View Data'[EmailAddress]
    )
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[divyed]

Hello @et123 ,

you can try below dax :

ViewsPerHour =
CALCULATE(
COUNTROWS(Events),
HOUR(Events[Datetime]) = HOUR(TODAY()) -- Replace TODAY() with a specific datetime if needed
)

I hope this helps.

Did I answer your query ? Please mark this as solution if this helps , Kudos are appreciated.

Cheers

[rajendraongole1]

Hi @et123 - you can create a DAX measure that groups the records by hour and ensures duplicate logins within the same hour for the same user are not double-counted.

UniqueViewsPerHour =
VAR UserHourGroups =
ADDCOLUMNS(
'View Data',
"HourKey",
CONCATENATE(
FORMAT('View Data'[DateCreated], "YYYY-MM-DD HH"),
'View Data'[SignIn]
)
)
RETURN
DISTINCTCOUNT(UserHourGroups[HourKey])

Try this and let me know if any.

[et123]

I tried and I get this error