HIstogram measure

[Anonymous]

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

[daxer-almighty · Accepted 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
)

[Anonymous]

Thanks a lot, its working great