Login frequency group and users count

[XuanHau]

Hi guys,

I have 2 tables: one for the history log (table below) and the other for the Date table. These 2 tables have a relationship via the Date column.

Account	Date	Type
A	10/05/2024	active
A	27/06/2024	active
A	28/06/2024	active
A	28/06/2024	locked
B	15/06/2024	active
C	19/06/2024	active
C	20/06/2024	active
D	19/06/2024	active
D	20/06/2024	active

I want to create a page with a Date slicer to filter data and a chart like this:

When choosing a date slicer, we will group the users who have the same number of login dates and count how many accuounts are in the same group and type.

For example, based on the data above, In June we have:

• 3 accounts (A, C, D) have logged in 2 times with the type "active"
• 1 account (A) has logged in one time with the type "locked"
• 1 account (B) has logged in one time with the type "active"

chart

How can I create this kind of chart? 

Any help is much appreciated!

Thank you in advance!

[amitchandak · Accepted solution]

@XuanHau , You need dynamic segmentation on measure

refer

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[XuanHau]

Hi @Ashish_Mathur

I used your suggestion to test with my data as well, and it works.

I very appreciate your help! 

[Ashish_Mathur]

You are welcome.

[DataNinja777]

Hi @XuanHau ,

In addition to @amitchandak 's technical recommendations, there are also other ways to achive your required output, like for example, just utilizing calculated columns.  

You can create calculated columns like below:

And then, you can create a measure like below from the calculated column above:

Distinctcount = DISTINCTCOUNT('Table'[What to count])

For the x-axis (field of number of times), you can also create a calculated column like below.

And put that on the stacked column chart to produce the same output you required.  

I attach a pbix file as an example.

[XuanHau]

@DataNinja777I also tried the way you suggested, but it works only if we use the slicer for one month. If we use multiple months or a date slicer, the results might not work well. I found another way to create the chart. Anyway, thank you for your ideas and help 👍

[amitchandak · Accepted solution]

@XuanHau , You need dynamic segmentation on measure

refer

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

[XuanHau]

@amitchandakThat's a good video. After following your guide, it's actually quite technically complicated for me, but finally, I can create the chart I want. Thank you so much for your help!