Daily unique visitors + Number of sessions per day

[beaoliv123-_]

Hi

I'd like to know if someone can help me calculate the follwing measures:

Daily Unique Visitors:

I have a table with all of my date, in which i have a column called ID. This ID identifies each client.

That means that every time a client starts a session, I'll have on the database a row with the identification of the client.

I have already identified the Unique Visitors, accordingly to the dates filtered on the report, so, if I have a visualization withthe data organized by week, I'll have the Unique visitors calculated according to the week.

However, I would like to have the same logic of the measure but counted for the day. I mean, instead of the calculationbeing accordingly to the data filtered, I'd like the visitors to be counted once by day. So if a client visits my website 1 time each day in a specific week, i would have 1 unique visitor according to the measure I already have, but I would have 7 visits (by day) according to this new measure I would like to have.

Number of clients that have 1 session, 2 session, 3 sessions, 4 sessions, 5 or more sessions

In this measure I would like to know, by day, how many sessions does each client have.
So, I want to count how many clients have 1 session per day, how many have 2 sessions per day, etc.

Example:

Day 1      Day 2

Client A   Client A

Client A   Client B

Client B   Client C

Client C   Client C

Client C   Client D 

Client C    Client D

According to the data, I would have, on the Day 1:

1 client with 2 sessions

1 client with 1 session

1 client with 3 sessions

On day 2:

2 clients with 1 session

2 clients with 2 sessions

For each number of sessions I would like to have a measure:
Measure 1 = Numer of clients with 1 session
Measure 2 = Number of clients with 2 session

and so on

Thank you so much

I'd really appreciate if you can help me

[v-rongtiep-msft · Accepted solution]

Hi @beaoliv123-_ ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUXBUUFBQ0oHzlGJ1cEg5IUs5IcSdkcWdCYu74BaPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Day 1    " = _t, #"Day 2" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Day"}, {"Value", "Client"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then add an index column.

Finally create 2 measures.

Measure1(Numer of clients with 1 session) =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Client] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Day] = SELECTEDVALUE ( 'Table'[Day] )
            && [Measure] = 1
    )
)

Measure2( Number of clients with 2 session) =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Client] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Day] = SELECTEDVALUE ( 'Table'[Day] )
            && [Measure] = 2
    )
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[beaoliv123-_]

Thank you so much!

[ToddChitt]

You can create a DAX table that summarizes the data and reports an aggregate as a column. Us the SUMMARIZE DAZ function. Then in that table, do a Count measure on the [Count Of Rows] aggregated column.