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

How to count occurrences of unique IDs in a table, responsive to a date filter

Hi everyone - I'm stuck. I am trying to identify returning clients by how many times they appear in a data set. The goal is to (a) identify repeat users , (b) segment them by how many times they were served within a date range, and (c) allow the user to change that data range. (a) and (c) are must have features, (b) is a nice to have feature. 
 
It seems like I need to create a table that calculates a [count of visits] for each [Unique_ID], and then build a second measure from that. But I'm missing something.
 
My source table is structured like this:
 
Row_ID
Unique_IDDateOther info
1Person_A1/1/2020etc
2Person_A1/7/2020etc
3Person_A1/10/2020etc
4Person_A2/5/2020etc
5Person_B1/1/2020etc
6Person_B2/1/2020etc
7Person_C1/25/2020etc
8Person_C1/28/2020etc
9Person_C2/1/2020etc
10Person_C2/10/2020etc
11Person_C3/5/2020etc
12Person_C3/17/2020etc
13Person_D1/30/2020etc
14Person_E1/1/2020etc
15Person_E2/18/2020etc
 
I can create a calculated table that looks like this:
 
Clients_Table = SUMMARIZE(    Source_Table,    Source_Table[Unique_ID],  "Count of visits",  COUNTA(Source_Table[Row_ID]) )
 
Unique_IDCount of visits
Person_A4
Person_B2
Person_C6
Person_D1
Person_E2
 
Using this table, I can identify repeat users or create bins for a histogram. Excellent! But, when I apply a filter to [Date] in my report pages (using a filter visual), the [Clients_Table] table does not reflect the new date range even though the visuals based on [Source_table] do update.
 
Is there some way to apply my filter to the calculation that creates my [Clients_Table]? Or perhaps some other approach to make this dynamic, like creating a measure that imputes this table? Or something else I'm not thinking of?
 
Please let me know if you have any guidance, or if you have encountered this before. Thanks for any tips!
 
(edited for clarity)
1 ACCEPTED SOLUTION

Hi @Anonymous ,

I create a sample pbix file(see attachment) for you, please check whether that is what you want.

How to count occurrences of unique IDs in a table, responsive to a date filter.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

How to count occurrences of unique IDs in a table, responsive to a date filter.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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

https://youtu.be/CuczXPj0N-k

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.