Using a date table to filter makes it impossible to keep 1 to M relationship

[kazuma6666]

Hello,

I'm trying to get the sums of amounts found in invoices and credit notes for the memberships, as well as their nominal codes (internal label for grouping).

I will start explaining the structure a bit. Our memberships have a validity period of one to six years. They start at the beginning of the year, and end at the end of the year. To be able to filter years, I created a new field containing all the dates from beginning of each membership to the end of the membership. This is the code I used:

List.Transform(
  {
  Number.From(Date.From([si_validfrom]))
  ..Number.From(Date.From([si_validto]))
  },
  each Date.From(_)
))

Si_validfrom and si_Validto are the start date and end date of the membership.

I then extended this field so I have the lists of all dates.

I have linked this to a date table, and using that I can easily filter the memberships for a specific year.

This is something I learned recently when creating a report for membership subscribers on this forum.

The problem is that when I link the date table to my membership table, I can't keep the relationship between membership and invoices. There can be multiple invoices/credit notes for a single membership. So for relation integrity, I need to remove the relation between membership and invoices.

And the values I need (amount and nominal codes) are in the InvoiceDetails table (the lines of invoices).

I am not sure how to get this info as I cut the link between invoices and membership.

Thanks in advance for your time!

[kazuma6666]

Hello again @BA_Pete 

I finally have time again to look into this report. I have been reading a bit about star schemas, and watching tutorials. 

I think my membership table is already a fact table. Next I take a copy of the membership table, remove all data except dates and membership ID and I have a dimension table for the dates. I can then expand the dates using the same method I used before. At that point, the link between my Membership table and the invoices should work, because I won't need a  M to N relationship anymore. I should be able to use the dates as slicer again to filter my reports.

I will start working on this now. Thanks for your help!

Ben.

[BA_Pete]

Hello again @kazuma6666 ,

Can you share a screen shot or an example of the structure and types of data in your invoice table please?

It just sounds like you need a member dimension table, so shouldn't be too difficult to get this fixed up for you.

Pete

[kazuma6666]

Hello again @BA_Pete!

Thanks for looking into this too 🙂

Here is a screenshot of the invoices table in PBI:

_si_membershipid_value is the key in the membership table. That is the link to it.

Please let me know if you need any more information.

[BA_Pete]

Cool. And which field was related to your memberships table? Was it on [_si_membershipid_value]?

Pete

[kazuma6666]

Yes, [_si_membershipid_value] is related to the membership table.

[BA_Pete · Accepted solution]

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

[kazuma6666]

Hello @BA_Pete 

I haven't been able to work on this project this week, but I'll let you know as soon as I have more time to look into it. I'm having issues with stuff in production which is of course a priority.

Thanks for all your answers 🙂