How to find the Unique users per day from a single Table with columns available?

[ssspk]

Hello Team,

I have to find the DAX Query or Measure for unique users per day based on the below python code,

events_per_user_per_day = all_events.groupby([pd.Grouper(key='servertime', freq='D', label='left'), 'user'])[['appId']].count()
f = events_per_user_per_day.groupby(level="servertime").size().plot()

My SQL query is like below,

SELECT   DATE(servertime) AS Day,  user AS User,  COUNT(appid) AS EventsPerUserPerDay

FROM  traces

INNER JOIN traces on data.type = type.id

WHERE typename in ('bookmark','app')

AND appid >  0

GROUP BY  DATE(servertime), user

ORDER BY 1 DESC ; 

My DAX Measure,

_UniqueClicksPerDay =

VAR ClicksPerUserAndAppPerDay =

    SUMMARIZE(

        'data',

        'data'[servertime],

        'data'[user],

        'data'[appid],

        "ClicksCount", COUNTROWS('data')

    )

RETURN

SUMMARIZE(

    ADDCOLUMNS(

        GROUPBY(

            ClicksPerUserAndAppPerDay,

            'data'[servertime],

            'data'[appid],

            "UniqueUsers",

            DISTINCTCOUNT('data'[user])

        ),

        "UniqueClicks", [ClicksCount] / [UniqueUsers]

    ),

    'data'[servertime],

    'data'[appid],

    "UniqueClicks", [UniqueClicks]

)

How to find the 'Unique user per day' based on the below details,

TableName:data

Column1:servertime

Column2:user

Column3:appid

There are n number of application available and each has different appid.each click creates one entry in db.

User who ever clicks the application it creates an entry in database as ex: user: 45296 appid: 460 servertime: datatime

How to form a DAX or Measure ot Table?

Thanks and Regards,

Siva

[v-jialongy-msft]

Hi @ssspk 

Please try the following methods:

1: DAX Measure for Unique Users Per Day

If you want a measure that you can use in visuals to dynamically calculate unique users per day based on the current filter context, use the following DAX measure:

Unique Users Per Day =
COUNTROWS(
SUMMARIZE(
'data',
'data'[servertime],
'data'[user]
)
)

This measure counts the unique combinations of `servertime` and `user`, effectively giving you the number of unique users per day. It's worth noting that the `servertime` column should be a date or datetime type. If `servertime` includes times, you might need to convert it to a date-only format within the measure, like so:

Unique Users Per Day =
COUNTROWS(
SUMMARIZE(
'data',
DATEVALUE('data'[servertime]),
'data'[user]
)
)

2: Calculated Table for Unique Users Per Day

If your goal is to create a calculated table that lists each day with the count of unique users for that day, you can use the following DAX expression:

Unique Users Per Day Table =
SUMMARIZE(
'data',
DATEVALUE('data'[servertime]),
"UniqueUsers", DISTINCTCOUNT('data'[user])
)

This calculated table will have two columns: one for the day and one for the count of unique users for each day ('UniqueUsers').

Best Regards,

Jayleny

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