cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EWBWEBB
Helper I
Helper I

Probability of belonging to group

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

4447001/10/20211 
4447102/10/20211 
4447203/10/2021 1
4447304/10/202111
4447405/10/20211 
4447506/10/20211 
4447607/10/202111
4447708/10/2021 1
4447809/10/2021 1

 

The Customer Data looks like this


CustID,CustName,LogName,Date,DateKey

12345J.BloggsAttended01/10/202144470
12345J.BloggsAttended02/10/202144471
12345J.BloggsX103/10/202144472
12345J.BloggsHoliday04/10/202144473
12345J.BloggsAttended05/10/202144474
12345J.BloggsAttended06/10/202144475
12345J.BloggsHoliday07/10/202144476
12345J.BloggsX108/10/202144477
12345J.BloggsX109/10/202144478
12345J.BloggsX110/10/202144479
12345J.BloggsX111/10/202144480
12345J.BloggsX112/10/202144481
12345J.BloggsX113/10/202144482
12345J.BloggsX114/10/202144483
12345J.BloggsX115/10/202144484
34567J.SmithX101/10/202144470
34567J.SmithX102/10/202144471
34567J.SmithHoliday03/10/202144472
34567J.SmithAttended04/10/202144473
34567J.SmithX105/10/202144474
34567J.SmithX106/10/202144475
34567J.SmithAttended07/10/202144476
34567J.SmithAttended08/10/202144477
34567J.SmithAttended09/10/202144478
34567J.SmithX110/10/202144479
34567J.SmithX111/10/202144480
34567J.SmithX112/10/202144481
34567J.SmithX113/10/202144482
34567J.SmithX114/10/202144483
34567J.SmithX115/10/202144484
891011M.JordanHoliday01/10/202144470
891011M.JordanHoliday02/10/202144471
891011M.JordanHoliday03/10/202144472
891011M.JordanAttended04/10/202144473
891011M.JordanX105/10/202144474
891011M.JordanX106/10/202144475
891011M.JordanX107/10/202144476
891011M.JordanX108/10/202144477
891011M.JordanX109/10/202144478
891011M.JordanX110/10/202144479
891011M.JordanX111/10/202144480
891011M.JordanX112/10/202144481
891011M.JordanX113/10/202144482
891011M.JordanX114/10/202144483
891011M.JordanX115/10/202144484
891011E.PrentissAttended01/10/202144470
891011E.PrentissX102/10/202144471
891011E.PrentissHoliday03/10/202144472
891011E.PrentissAttended04/10/202144473
891011E.PrentissAttended05/10/202144474
891011E.PrentissAttended06/10/202144475
891011E.PrentissAttended07/10/202144476
891011E.PrentissX108/10/202144477
891011E.PrentissX109/10/202144478
891011E.PrentissX110/10/202144479
891011E.PrentissX111/10/202144480
891011E.PrentissX112/10/202144481
891011E.PrentissX113/10/202144482
891011E.PrentissX114/10/202144483
891011E.PrentissX115/10/202144484

 

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

1 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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

 

@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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors