Cardinality between lookup table and fact table

[Anonymous]

Hi.

I've been having an issue with table relationships and cardinality that's breaking most of my visuals. 

Basically, I have a lookup table with one column (unique user_ids), that's connected to many tables with non-unique user_ids. However, it's also connected to a table (call it A) with unique user_ids (more information about the user profile). The initial cardinality between the lookup table and table A was 1-to-1 with a filter direction of both. I changed it to 1-to-many and single filter direction. Table A is also connected with a date lookup table. I can't revert back to 1-to-1 because it introduces ambiguity between the date lookup table and table B. I don't quite understand this.

The problem is when I use the user_id from the lookup table, it doesn't work; charts display the total value without being filtered down based on gender, age, etc. for example. What could be the issue? Am I doing the lookup tables wrong? How do you usually connect two tables through a unique column? Not sure if I explained myself clearly but any help would be appreciated. I attached a screenshot of the relationships (sorry for the clutter, still new to all of this). Let me know if you need more info. Thank you.

[parry2k · Accepted solution]

@Anonymous there are few ways to solve this, make date and a table relationship to inactive and make user lookup and a table as 1 to 1, logically that is 1 to 1 and create a measure to calculate the user by using USERELATIONSHIP in the measure, something like this:

User Count Measure = 
CALCULATE ( COUNTROWS ( TableA ), USERELATIONSHIP ( DateTable[Date], TableA[Created_At] ) )

and in line chart use above measure.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous question is where are you using that relationship? Are you that in any matrix/kpi/visual?

[Anonymous]

Ah yes, I have a line chart with date as an axis and count of users (count of user_id) as values. So, it shows how many users subscribe every day/month. I use the user_id from the user lookup table but it doesn't work; the line chart returns a straight line of all the users. Using the user_id from table A works of course. 

[parry2k]

@Anonymous how the relationship between the date and A table is used?

[Anonymous]

The date column in the date lookup is connected with the created_at date column in the table A. When users create their profile, they are appended to table A and the date of their profile creation is added.  

[Anonymous]

Don't like bumping my own questions but I don't want it to be buried. Apologies if this isn't allowed. 

If you guys have any idea of what the problem could be, I would appreciate the help! Thanks.

@amitchandak @parry2k @selimovd @Fowmy