topic HIstogram measure in DAX Commands and Tips

HIstogram measure

Anonymous — Thu, 10 Dec 2020 14:43:31 GMT

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

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.

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

Re: HIstogram measure

daxer-almighty — Thu, 10 Dec 2020 16:06:48 GMT

// 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 )

Re: HIstogram measure

Anonymous — Fri, 11 Dec 2020 08:57:36 GMT

Thanks a lot, its working great