Forum Discussion
Cardinality between lookup table and fact table
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.
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.⚡
6 Replies
- parry2k
Super User
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.⚡
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.
- parry2k
Anonymous how the relationship between the date and A table is used?
- parry2k
Anonymous question is where are you using that relationship? Are you that in any matrix/kpi/visual?
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.