Forum Discussion
HIstogram measure
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
// 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 )
2 Replies
// 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 )