Finding results from 5 tables in 1 table

[trobey]

I have 5 tables all containing email addresses and need to compare these email addresses to our membership base to determine how many events someone attended.   I have set up the relationships from the tables back to the main table (members list).   But I can't figure out how to get it show that a member attended 3 events or 4 events and which events they attended.   Maybe this can't be done but it seems like it should. 

[Phil_Seamark]

It can definitely be done.

I suggest combining your 5 tables into 1 much longer table.  This can be done in DAX using the UNION function, or depending on your data source, you can acheive this upstream.  You can also do this in the Query Editor.

Once you have comined into a single table, you can create a COUNT, (or distinct count) measure that counts the number of events the member attended.

Would probably need to see a little more detail of your columns before providing a suggested formula.

But definitely combine your 5 tables into 1 table.

[trobey]

Thanks for your response, let me provide more information. 

Membership database contains email address for all our members which I have pulled into BI.  I have also brought in the 5 tables/spreadsheets of attendee rosters to 5 different events.  I need to determine of our members how many have attended each of the events as well as how many attended 1 of 5, 2 of 5, 3 of 5, 4 of 5 or all.   Combined all 5 into one spreadsheet containing two columns, email and event name.   Problem with that is when I try to establish the relationship to their email in our membership database it won't connect because there isn't a unique value because some people have attended more than one event.  

Does that help? 

[Phil_Seamark]

If you create a measure in your Event table that counts the distinct cound of event names, this should give you the value per email between 1 & 5

Do you need to create a relationship to your Membership table at all?

The other part of your query can probably be achieved without a relationship too.  That is to simply add the Eventname column as a slicer and this will show you who (and how many) people attended a particular event.

[trobey]

I need to create the relationship to the membership table so I know if the person attending the event was a member.  

[Phil_Seamark]

Ok thats fine and should work ok so long as your membership table contains unique email addresses for you to link over.

I'd still create the measures on your fact table (the one with email & eventname) and simply create it as something like

Distinct Events = CALCULATE(DISTINCTCOUNT('event table'[eventname]))

[trobey]

I think that is where I'm running into the problem.   the membership list may have unique email addresses but the event list does not.   Multiple events attended by the same person which means their email is on the list mulitple times.  

I have 1,138 rows with 1,038 distinct values in the email column of the table where I combined all of the events.   So I have a number of duplicates.   I can't remove them.  And I used the formula you provided and it returned 1 for all of the emails. 

I obviously have done something very wrong or it can't be done.   

[Phil_Seamark · Accepted solution]

Your membership table should only have unique email addrss per user - which it sounds like it does.

The event table will have some email addresses multiple times - which it sounds like it does.

This is all good.

You can create a relationship between the membership table and the event table using the email column and this will create a 1 to many relationship.

The key is you need to create the meaure on the Event table and not the membership table as it needs to count the distinct number of emails in the table where you can have multiple emails.