Function to count one login value per day, Shown in bins

[Anonymous]

Hello, I have been learning a lot form the forum but I am breaking my head over the following:

I would like to show the number of days users have loggedin and aggregate those in bins in a bar chart. 

With = DistinctCount([UserID-DateOfLogin]) I can show the following graph.

[UserID-DateOfLogin] = a combination of the columns [UserID] and [DateOfLogin]

I have grouped my data on [UserID] and [DateOfLogin] so I do not have any duplicates per user per day.

What I would like to show is a graph which shows the number of users that loggedin 0 times, 1 - 5 times, 6 - 10 times, 11 - 15 times and 15 > times. Over a dynamic period specified by a date slicer on the same BI. (I already have a data calendar setup and linked to the "DateOfLogin" column in my Logins tabel.

I think I am nearly there but I cannot get there.

Hope someone can help. 

My data model:

[amitchandak]

@Anonymous , Refer, if this can help

https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Mariusz]

Hi @Anonymous 

Try these two articles.

https://www.daxpatterns.com/dynamic-segmentation/

https://www.daxpatterns.com/static-segmentation/

by the way, you can avoid creating extra columns by creatin measure like.

Measure = 
COUNTROWS(
    SUMMARIZE( 
        YourTableName, 
        YourTableName[UserID], 
        YourTableName[DateOfLogin]
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hello @Mariusz @amitchandak 

Thank you for your quick response!

The Groups / Banding part I have already in my BI:

Bins = CALCULATE(COUNTROWS(FILTER(LoginData;AND([LoginDays] >= MIN(BinUsoCrm[Min]);[LoginDays] <= MAX(BinUsoCrm[Max])))))

LoginDays = DISTINCTCOUNT(LoginData[UserIDDateOfLogin])

Min Max Name

0     0      0

1     5      1-5

6     10    6-10

11   15    11-15

16   1000  15 >    

But what is missing is the combination of that with the DISTINCTCOUNT(UserIDDateOfLogin) into a graph. It is really driving me crazy becuase I see the parts but I cannot get them to work together.

Thomas

[Mariusz · Accepted solution]

Hi @Anonymous 

Try this

Bins = 
CALCULATE(
    [LoginDays];
    FILTER(
        LoginData;
        AND( [LoginDays] >= MIN( BinUsoCrm[Min] ); [LoginDays] <= MAX( BinUsoCrm[Max] ) )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn