Solved: HIstogram measure

Anonymous · ‎12-10-2020

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.

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 · ‎12-10-2020

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

View solution in original post

daxer-almighty · ‎12-10-2020

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