Forum Discussion
Helper ILogin frequency group and users count
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!
XuanHau , You need dynamic segmentation on measure
refer
Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ
Hi,
PBI file attached.
Hope this helps.
7 Replies
- amitchandak
Super User
XuanHau , You need dynamic segmentation on measure
refer
Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ
XuanHauamitchandakThat'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!
- 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 👍
- Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
- XuanHau
I used your suggestion to test with my data as well, and it works.
I very appreciate your help!
- Ashish_Mathur
You are welcome.
