Advanced Data Model: Joining single table to multiple tables

[rayishome]

The Data Model consists of 4 tables.  2 of the tables are duplicates.  

Voters (Single records per voter)

1. VoterID
2. VoterCnt=1 (to sum up)
3. VotedInPerson= (0 or 1) to Sum Up

Votes1 (Can vote on multiple items)

1. VoterID
2. VoteObject
3. VoteCategory

Votes2 (Copy of Votes1 used to build cross joins)

Category (Single list of Vote Categories that will fitler both Vote1 and Vote2)

Relationships

Vote1 (Many to Many) filtering Voters

Vote2 (Many to Many) fitlering Voters

Caterogy (1 To Many) to Vote1 and Vote2 (This is not working)

The goal of this model:

1. is to produce distinct counts by Summing vs using Distinct Counts on the Voter table. 
2. Also to cross fitler.  When we select a VoteObject in Vote1 we can see who also voted on another VoteObject from the Vote2 table.  
3. Allow Category to filter both the Vote1 and Vote2 tables on the same Category so the users doesn't have to select Category on two fitlers. (this is now working, but all of the relationships are one way towards the Voter table)

Thanks!

[v-lili6-msft]

hi  @rayishome 

I don't think you need two duplicate tables (Votes1& Votes2).

Since Vote1 (Many to Many) filtering Voters and Vote2 (Many to Many) fitlering Voters, these two relationshps are all "Both", so you could not use a table create two relationship with Votes1& Votes2, it likes you could create two relationship between two tables.

what is your expected output for "cross joins"? please share some sample data and your expected output, and I will give you further help.

Regards,

Lin

[sanimesa]

@rayishomeHow are you producing the Category dimension table? If it contains non-unique values, it will not work in your scenario.

Please check the data in that table.