Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I would like to create a graph like a kind of Histogram.
The data is about logging into an application.
I have a row per user connected to the application, and the same user can be connected several times in the same day.
User Date
1 1/01/2020
2 1/01/2020
3 1/01/2020
4 1/01/2020
2 2/01/2020
3 2/01/2020
1 3/01/2020
1 3/01/2020
1 3/01/2020
2 3/01/2020
3 3/01/2020
What I would like is to have a bar chart counting the number of distinct users, grouped by the number of days connected in a month.
So
1 day-- 1 user (4)
2 days-- 1 user(1)
3 days-- 2 users (2,3)
I have been trying to solve this with a measure or with a Summarize table, but it didn't work for me.
Any clue?
Thanks in advance
Solved! Go to Solution.
// You have to create a disconnected table
// that will store the numbers (of days) you want.
// That would be 1,2,3,...,N. N will probably be 31
// but my measure works for ANY PERIOD OF TIME, not
// only a month.
//
// Let's call it 'Connected Days'. It'll have
// just one column: 'Connected Days'[Number Of Days].
// Then assume you've also got the table as
// shown in your post. You can then write a measure
// that'll work against the 'Connected Days' table.
[# Users Connected] =
IF( HASONEFILTER( 'Connected Days'[Number Of Days] ),
// For the visible Number of Days find the number
// of users in your T table (honoring all the
// other possible filters) that were connected
// during the currently visible period for that
// number of days.
var __selectedNumOfDays =
SELECTEDVALUE( 'Connected Days'[Number Of Days] )
var __usersWithNumberOfDays =
ADDCOLUMNS(
DISTINCT( T[User] ),
"@NumOfDays",
CALCULATE(
DISTINCTCOUNT( T[Date] )
)
)
var __numberOfDaysWithNumOfUsers =
GROUPBY(
__usersWithNumberOfDays,
[@NumOfDays],
"@NumOfUsers",
SUMX( CURRENTGROUP(), 1 )
)
var __output =
MINX(
filter(
__numberOfDaysWithNumOfUsers,
[@NumOfDays] = __selectedNumberOfDays
),
[@NumOfUsers]
)
return
__output
)
// You have to create a disconnected table
// that will store the numbers (of days) you want.
// That would be 1,2,3,...,N. N will probably be 31
// but my measure works for ANY PERIOD OF TIME, not
// only a month.
//
// Let's call it 'Connected Days'. It'll have
// just one column: 'Connected Days'[Number Of Days].
// Then assume you've also got the table as
// shown in your post. You can then write a measure
// that'll work against the 'Connected Days' table.
[# Users Connected] =
IF( HASONEFILTER( 'Connected Days'[Number Of Days] ),
// For the visible Number of Days find the number
// of users in your T table (honoring all the
// other possible filters) that were connected
// during the currently visible period for that
// number of days.
var __selectedNumOfDays =
SELECTEDVALUE( 'Connected Days'[Number Of Days] )
var __usersWithNumberOfDays =
ADDCOLUMNS(
DISTINCT( T[User] ),
"@NumOfDays",
CALCULATE(
DISTINCTCOUNT( T[Date] )
)
)
var __numberOfDaysWithNumOfUsers =
GROUPBY(
__usersWithNumberOfDays,
[@NumOfDays],
"@NumOfUsers",
SUMX( CURRENTGROUP(), 1 )
)
var __output =
MINX(
filter(
__numberOfDaysWithNumOfUsers,
[@NumOfDays] = __selectedNumberOfDays
),
[@NumOfUsers]
)
return
__output
)
Thanks a lot, its working great
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |