Probability of belonging to group

[EWBWEBB]

Hello,

I'm not sure this is even possible with DAX but I'm keen to understand if it is and how i might go about it. 

What I am looking to achieve is:

Based on the assigned code on a given date how likely, or how I might be able to ascertain, which group someone belongs to based on the dates of events they have attended.

My event table looks like this ( I can change this if it makes this more achieveable as it's a small manually populated worksheet in excel)

DateKey,EventDate,CycleClub,CarClub

44470	01/10/2021	1
44471	02/10/2021	1
44472	03/10/2021		1
44473	04/10/2021	1	1
44474	05/10/2021	1
44475	06/10/2021	1
44476	07/10/2021	1	1
44477	08/10/2021		1
44478	09/10/2021		1

The Customer Data looks like this

CustID,CustName,LogName,Date,DateKey

12345	J.Bloggs	Attended	01/10/2021	44470
12345	J.Bloggs	Attended	02/10/2021	44471
12345	J.Bloggs	X1	03/10/2021	44472
12345	J.Bloggs	Holiday	04/10/2021	44473
12345	J.Bloggs	Attended	05/10/2021	44474
12345	J.Bloggs	Attended	06/10/2021	44475
12345	J.Bloggs	Holiday	07/10/2021	44476
12345	J.Bloggs	X1	08/10/2021	44477
12345	J.Bloggs	X1	09/10/2021	44478
12345	J.Bloggs	X1	10/10/2021	44479
12345	J.Bloggs	X1	11/10/2021	44480
12345	J.Bloggs	X1	12/10/2021	44481
12345	J.Bloggs	X1	13/10/2021	44482
12345	J.Bloggs	X1	14/10/2021	44483
12345	J.Bloggs	X1	15/10/2021	44484
34567	J.Smith	X1	01/10/2021	44470
34567	J.Smith	X1	02/10/2021	44471
34567	J.Smith	Holiday	03/10/2021	44472
34567	J.Smith	Attended	04/10/2021	44473
34567	J.Smith	X1	05/10/2021	44474
34567	J.Smith	X1	06/10/2021	44475
34567	J.Smith	Attended	07/10/2021	44476
34567	J.Smith	Attended	08/10/2021	44477
34567	J.Smith	Attended	09/10/2021	44478
34567	J.Smith	X1	10/10/2021	44479
34567	J.Smith	X1	11/10/2021	44480
34567	J.Smith	X1	12/10/2021	44481
34567	J.Smith	X1	13/10/2021	44482
34567	J.Smith	X1	14/10/2021	44483
34567	J.Smith	X1	15/10/2021	44484
891011	M.Jordan	Holiday	01/10/2021	44470
891011	M.Jordan	Holiday	02/10/2021	44471
891011	M.Jordan	Holiday	03/10/2021	44472
891011	M.Jordan	Attended	04/10/2021	44473
891011	M.Jordan	X1	05/10/2021	44474
891011	M.Jordan	X1	06/10/2021	44475
891011	M.Jordan	X1	07/10/2021	44476
891011	M.Jordan	X1	08/10/2021	44477
891011	M.Jordan	X1	09/10/2021	44478
891011	M.Jordan	X1	10/10/2021	44479
891011	M.Jordan	X1	11/10/2021	44480
891011	M.Jordan	X1	12/10/2021	44481
891011	M.Jordan	X1	13/10/2021	44482
891011	M.Jordan	X1	14/10/2021	44483
891011	M.Jordan	X1	15/10/2021	44484
891011	E.Prentiss	Attended	01/10/2021	44470
891011	E.Prentiss	X1	02/10/2021	44471
891011	E.Prentiss	Holiday	03/10/2021	44472
891011	E.Prentiss	Attended	04/10/2021	44473
891011	E.Prentiss	Attended	05/10/2021	44474
891011	E.Prentiss	Attended	06/10/2021	44475
891011	E.Prentiss	Attended	07/10/2021	44476
891011	E.Prentiss	X1	08/10/2021	44477
891011	E.Prentiss	X1	09/10/2021	44478
891011	E.Prentiss	X1	10/10/2021	44479
891011	E.Prentiss	X1	11/10/2021	44480
891011	E.Prentiss	X1	12/10/2021	44481
891011	E.Prentiss	X1	13/10/2021	44482
891011	E.Prentiss	X1	14/10/2021	44483
891011	E.Prentiss	X1	15/10/2021	44484

They are joined on the DateKey

X1 indicates available but not attended

My expectation here would be that J.bloggs is in the Cycle Club as they have been in attendance on more of the dates when there was a cycle club event. and J.Smith would be in CarClub as they have attended. M Jordan would be unknown as only attended on the date both clubs had an event.

If there is also a way to indicate the degree to which they are likely to be in a particular club that would be further helpful.
For example:

J.Bloggs is 100% likely to be in CycleClub as they attended 100% of events.

M.Prentiss is possibly in CycleClub as they attended all events except one where they were X1 (available).

I'd like to be able to display this in a table with the customers name & ID then the club they most likely belong to and the level of confidence that there is they belong to that club.

Genuinly no idea where to start with this!

Hopefully this makes sense

[Greg_Deckler]

@EWBWEBB Are the 1's in the first table the days when the club met? What are the days where there are no 1's, just days that there was no meeting for either?

[EWBWEBB]

@Greg_Deckler yep, the one indicates the dates the club met.
There shouldnt be any completely blank dates (unless I put one in by accident).

But if there is then yes it would indicate there was no meeting for either and the row could feasibly be removed to make things easier.

[Greg_Deckler · Accepted solution]

@EWBWEBB OK, here is a start at least. Probably can be improved. PBIX is attached below signature as I had to make a couple model changes.

Measure = 
    VAR __Dates = DISTINCT(SELECTCOLUMNS(FILTER('Customers',[LogName] = "Attended"),"__Dates",[Date]))
    VAR __Table = SUMMARIZE('Events',Events[Attribute],"__Count",COUNTX(FILTER(Events,[Value] = 1 && [EventDate] IN __Dates),[EventDate]))
    VAR __Max = MAXX(__Table,[__Count])
RETURN
    MAXX(FILTER(__Table,[__Count] = __Max),[Attribute])

[EWBWEBB]

@Greg_Deckler Thanks so much this is brilliant start.

I'm tinkering with it now to work around ties is in the MAXX where they have only attended on days where both are the same. 

But this get's me those initial comparison tables which I can play around with - thanks so much.