Problems with Data Model

I’m having trouble getting my dashboard to do what I want it to do and I think it might be because of the data model. Currently it is set up as:

When nothing is selected it looks fine:

image

However if I select a date on the slicer, some fields change but not the number of sign ups or a lot of the charts:

image

I tried changing the relationships in the data model like so:

Now everything works except for total income which is a mixture of one off donations and recurring donations summed is the Sales table.

image

I’m thinking I might need to rearrange my data but not sure how? Maybe there’s an easier fix?

Thanks

Jonathan

The U4 Dashboard table looks like more of fact table than a dimension table. I’d try to break that out a little more. Happen to be able to upload the pbix file?

@KieftyKids

you need to work out the primary keys for the dimensions required. The fact tables normally have the dates information. I would suggest the U4 Sales looks like the Control Ledger and the U4 Dashboard would be a sub-ledger of that data set. Which ever table has the unique sale ID should be the master table for the Facts. If a Sale ID is repeated this may be a sub ledger.

Maybe outline which tables are the unique primary keys?

Thanks for the replies.

I think I’m trying to run before I can crawl here.

I have three data sets and am not sure how to connect them.

  1. There’s the raw data from the supplier. The supplier gets digital leads and phones them in an attempt to convert them to a monthly gift. The raw data includes all contacts attempted. I know the cost of each digital lead source and the average cost per call.

  2. The successful monthly giving plans are then loaded onto our database (that’s the Dashboard table)

  3. The Sales data contains the recurring transactions and any additional one-off transactions we received

Currently the raw data is separate from the other two tables. There is however a unique identifier that connects the raw data to those customers that have been imported into our database. This number is contained in the Dashboards table and the first transaction only of the Sales table.

It would be nice to have all tables connected so the costs flow through etc.

Any additional thoughts would be most appreciated.

In my view all the tables you mentioned above sound like fact tables. So you need to have a think about what would be lookup tables you could setup that would logically connect to these fact tables - for example the Dates table.

Have you gone through this course here? These explains everything you need to know about this area of Power BI

Here’s some specific videos to also go through

To me some other lookup tables could be

Customers ( as you have customer IDs columns)
Regions (as you have location information)
…and then dates.

Also you should need have relationships between fact tables, so definitely remove this between the dashboard and sales tables.

Check out those videos there, they will point you in the right direction.

Sam