Forum Discussion
Helper IIUsing a date table to filter makes it impossible to keep 1 to M relationship
Ok. So you need to create a Members dimension table.
This will be a table with a UNIQUE column of membership ID's, then any number of other columns describing that member e.g. name, address, age, customer group, etc.
You may already have something like this set up, but let me know if not and we can look at easy ways to dynamically create one from your existing data (will probably be Monday now though I'm afraid).
You then relate dimMembers[MemberID] to both factMemSubs[MemberID] and factInvoices[MemberID]. You then use values from dimMembers in your visuals and this will correctly filter both of your fact tables to select only the member(s) you want to see.
You can also relate your calendar table to factInvoices[issueDate / dueDate / paidDate] and make these relationships inactive, so you can choose via your measures which date to use depending on what you are trying to calculate.
The model structure that you are aiming for is a STAR SCHEMA.
Pete
Super User
Cool. And which field was related to your memberships table? Was it on [_si_membershipid_value]?
Pete
Helper IIYes, [_si_membershipid_value] is related to the membership table.
- BA_Pete
Ok. So you need to create a Members dimension table.
This will be a table with a UNIQUE column of membership ID's, then any number of other columns describing that member e.g. name, address, age, customer group, etc.
You may already have something like this set up, but let me know if not and we can look at easy ways to dynamically create one from your existing data (will probably be Monday now though I'm afraid).
You then relate dimMembers[MemberID] to both factMemSubs[MemberID] and factInvoices[MemberID]. You then use values from dimMembers in your visuals and this will correctly filter both of your fact tables to select only the member(s) you want to see.
You can also relate your calendar table to factInvoices[issueDate / dueDate / paidDate] and make these relationships inactive, so you can choose via your measures which date to use depending on what you are trying to calculate.
The model structure that you are aiming for is a STAR SCHEMA.
Pete
- kazuma6666
Thanks Pete,
It's the same for me, I'll look at this on Monday, the weekend is starting for me too :).
I have never worked with dimensions table, so I may need more help to set this up, but at least I know what to look at.
Great stuff as always!
- BA_Pete
No worries. Give me ping on here Monday if you have any questions.
In the meantime, here's a bit of weekend reading for you 🙂
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Have a good one
Pete