Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Row_ID | Unique_ID | Date | Other info |
1 | Person_A | 1/1/2020 | etc |
2 | Person_A | 1/7/2020 | etc |
3 | Person_A | 1/10/2020 | etc |
4 | Person_A | 2/5/2020 | etc |
5 | Person_B | 1/1/2020 | etc |
6 | Person_B | 2/1/2020 | etc |
7 | Person_C | 1/25/2020 | etc |
8 | Person_C | 1/28/2020 | etc |
9 | Person_C | 2/1/2020 | etc |
10 | Person_C | 2/10/2020 | etc |
11 | Person_C | 3/5/2020 | etc |
12 | Person_C | 3/17/2020 | etc |
13 | Person_D | 1/30/2020 | etc |
14 | Person_E | 1/1/2020 | etc |
15 | Person_E | 2/18/2020 | etc |
Clients_Table = SUMMARIZE( Source_Table, Source_Table[Unique_ID], "Count of visits", COUNTA(Source_Table[Row_ID]) )
Unique_ID | Count of visits |
Person_A | 4 |
Person_B | 2 |
Person_C | 6 |
Person_D | 1 |
Person_E | 2 |
Solved! Go to Solution.
Hi @Anonymous ,
I create a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
Hi,
What result are you expecting? From the second table that you have shared, there are 2 clients that had 2 visits. So are you expecting to create a table with 2 in the row labels and 2 as the value against it. Likewise, if there were 5 clients which had 9 visits, you want to show 9 in the row labels and 5 as the value against it. Furthmore, you want all this to be sliced by any date range selected. Is my understanding correct? If not, then please clearly show the expected result.
Hello Ashish,
I at least need a count of clients that have multiple records (2+) within a time period, or a count of clients the number of visits (bins: 2 visits = 100 clients, 3 visits = 50 clients, etc). In that case the resulting table might look like this:
Count of visits (bins) Count of Clients
1 100
2 50
3 40
4 25
5 10
6 5
(sorry for the text, this site won't let me post a table in the reply)
But ideally, I would retain the Unique_ID in the results so that it could be associated with other data – for example, "30% of clients who visited 2+ times were over 60 year old". In that case I presume the resulting table would like this (below) and I could join this new [Clients_Table] table with my original [Source_Table]:
Unique_ID Count of Clients
Person_A 4
Person_B 2
Person_C 6
Person_D 1
Person_E 2
...
This would behave like a calculated column except that the values would change based on some other input, like a filter. This would be similar to a dynamc "parameter" in Tableau. As I mention in my original post, I can create this table using the SUMMARIZE function, but it is static and based on the entire table instead of a filtered range of data.
Does that answer your question?
Hi @Anonymous ,
I create a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
@Anonymous , You need to create an independent table using generate series or manually with bucket ranges like 0-1, 2-5 etc. You have to join this table in a measure with the Count of visits and group(values) the result at unique_id level and take it from there.
refer my video, I have used Range. You might have to use = join and use generateseries for discrete numbers
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |