Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

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

2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

// 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
Not applicable

Thanks a lot, its working great

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors