Best way to graph number of times a date appears in a column that is a list of dates?

[SteveCarter1]

Hi

Hoping someone has some creative thoughts that might help.

I have a WaitList table that has 2 columns: Client ID and 'Dates Waiting'. Dates Waiting is a colon separated list of dates.

I've copied sample data below.

I also have a separately  created date table, called MasterCalendar. It has a date column that is contiguous and spans the range of dates within the 'Dates Waiting' list.

If I create a line graph with X-Axis using the Date from the MasterCalendar, how could I create a measure to count the number of clients that have a matching date in the 'Dates Waiting' column?

I tried using M Query List expressions and was able to get the dates into a list then expand the list into new rows, then create a relationship between the MasterCalendar Date column and the expanded date column in the WaitList table. This was simple and worked fine as I just graphed the count of clients per date.

My problem is I have so many dates and clients that if I go back any further than about 6 months, the list expansion function blows out to 100's of billions of rows. On my desktop I stopped it after 2 days.  Uploading it to the PBI service, unsurprisingly, timed out after 2 hours, hence why I am looking for another way of approaching this problem.

ClientId	Dates Waiting
989618	3/08/2021:4/08/2021:5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
989065	3/08/2021:4/08/2021:5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
988057	4/08/2021:5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
985089	5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
989716	5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
973189	5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
989744	5/08/2021:6/08/2021:7/08/2021:8/08/2021:9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
869022	9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
931700	9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
935788	9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
973189	9/08/2021:10/08/2021:11/08/2021:12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
989821	12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
989492	12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021
989828	12/08/2021:13/08/2021:14/08/2021:15/08/2021:16/08/2021:17/08/2021:18/08/2021:19/08/2021:20/08/2021:21/08/2021:22/08/2021:23/08/2021:24/08/2021:25/08/2021:26/08/2021:27/08/2021:28/08/2021:29/08/2021:30/08/2021:31/08/2021:1/09/2021:2/09/2021

[amitchandak · Accepted solution]

@SteveCarter1 , Split by delimiter is the best way for this , as you can get dates to filter.

If you need only the total count of date -List.Count(Text.Split([Dates Waiting]))

Another way but that will work for one date at a time with same struture

Select a date from independent date slicer

measure =

var _dt = format(selectedvalue('date'[date]))

return

countrows(filter(Table, Search(_dt,[Dates Waiting],,0) >0))

[SteveCarter1]

Hi Amit

As usual, thanks a lot!  Nice benefit there is I also don't need a date relationship for it to work, which owkrs very much in my favor.