Power BI and Gift Table Column Expansions

@Mark Zigadlo
not sure what you mean by “gift_splits.id is not unique". It is unique to the gift split. So the issue likely has to do with what other one-to-many relationship you may have “expanded”.

a single gift with 5 gift splits will have 5 rows and each with its own unique gift splits id, however, if you also expanded fundraisers (for example, as it is another one-to-many relationship), and the single gift has 2 fundraisers, then you are going to get 10 rows of data for the combination of 5 splits times 2 fundraisers, there by making gifts split id showing twice (once for each fundraiser).

@Mark Zigadlo
expanding more than one-to-many relationship in a single data table is not a good idea, at most you should only expand one of such relationship, and keeping in mind that expanding that will cause your data table to duplicate columns that would (1) waste space in the power bi storage (local and cloud) and (2) potential double counting if not careful, such as the amount column that represent amount on the gift level

it is much better to have separate data table, where one is the main gift table that has all the one-to-one field (i.e. gift date, gift amount, gift id, gift type/subtype, etc) and then one table for each one-to-many relationship keeping the gift id for relationship linking (i.e. gift split table - gift id, campaign id, fund id, appeal id, package id, split amount) and (i.e. gift fundraiser - gift id, constituent id, amount credit to fundraiser) and (i.e. soft credit - gift id, constituent id, amount soft credited)