Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
Thank you so much!
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.
Proud to be a Super User! | |
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |