Hi Mynda,

I've been doing quite a lot of thinking about this and I'm really grateful for your insights and suggestions.  I reproduced the pivot that you sent a picture of and all was well, but I then started to do some thinking about it.  If the Towns table is the fact table, as it should be, then surely referential integrity requires it to be on the "one" side of the relationship, which isn't the case (see pic).

As soon as I switched the relationship around, the pivot broke (of course) and the same figure (642) was repeated in every cell of Sum of Club Size.  In order to reapir this, I had to create a calculated column using RELATED to bring that field across to the dimension table.  This became more obvious when I wanted to expand the tables by including extra facts for each Town and Area (see attached workbook).  This works, of course, as the pivot table is effectively just working on a single PowerPivot Table.

I'vecome to realise that Power Pivot relationships aren't the same as relational database relationships and I'll continue my journey.  Sorry this has been going round in circles and I'll quite understand if you've had enough of this conversation.  However, I'd be grateful if you could let me know if I'm now thinking along the right lines.

Thanks again.

Pieter

Hi Pieter,

You've got yourself confused. The dimension tables filter the fact tables, therefore the relationship was correct because the Alloc dim table filtered the Towns fact table. The direction of the arrow on the connector line indicates the filter direction. 

Mynda

Hi Mynda,

Don't know what was going on in my head to get Fact and Dimension tables 180 degrees wrong!  I've given myself a stern talking to and have created a small star relationship table, with tblAlloc as the Fact, and the others as Dimension tables, with the filter arrows all going the right way.  I also have re-done my measures without a single RELATED field to be seen; the result (attached) may not be the most efficient in terms of DAX, but it's given me wht I needed - as well as a lot of learning.

Many thanks again for your persistence.

Pieter

Hi Pieter,

While it may work, this is fundamentally the wrong approach. Fact tables have values, dim tables typically don't. You're forcing the Alloc table to be a fact table by assigning the measures to it. That doesn't make it a fact table.

This incorrect structure is forcing you to create measures that you don't even need. If you just follow my advice and structure your model the way I've shown you (see attached again with the new SecName field) it will be so much easier for you, and will prevent you running into problems further down the track as your model evolves. 

I feel like we're going around in circles. I show you how to structure it, you come back with the opposite. I show you again how to structure it and yet again you come back with the opposite. Perhaps there is a bigger picture you're not sharing with me that justifies why you're not taking my advice. If so, please share so I can give you the right advice the first time.

Mynda

Hi Mynda,

First a huge apology for upsetting you and I recognize that some of my questions seem contradictory – and sometimes just wrong.  I really wasn’t setting out to be difficult, it’s just that as I developed my very basic understanding, a number of different issues came up.

My first question was to ask how I could get a running set of subtotals for club numbers and the number of small clubs, based on a single table containing club memberships, with some members belonging to more than one club.  You kindly helped with this and provided an excellent solution.

Things developed and I should probably have set up a completely different situation, but I didn’t.  I wanted to be able to include multiple pieces of information specific to each town and area, and link them together.  I therefore put aside the “memberships” issue and included each town’s club size as a given number, as well as additional specific information – in this case, the Secretary’s name.  To balance things up, I also included the Area Secretary’s name.

I thus needed to glue together (allocate) towns and areas, noting that a Club may move from one area to another.

Your solution worked absolutely fine, and it seemed to me that your solution involved a bit of duplication, such as the Town name, Area name and Secretary name on each row of the alloc table.

It was at this point that I made my foolish mistake in getting “Fact” and “Dimension” tables mixed up!  I was also trying to separate out the information specific to each to Town (tblTowns) and to each Area (tblAreas).  From my earlier experience with databases, some years ago, it seemed reasonable to assign a primary key to each Town (TownID) and each Area (AreaID).  The idea was to simply enter the related, foreign, key into tblAlloc to draw together the two sets of information.  In this example the club size was actually a dimension of each club – perhaps not strictly correct, but it was specific to each club.

In the back of my mind I was then looking at adding the date of each (re)allocation to tblAlloc, together with, perhaps, any costs associated with the reallocation.  I was thinking of sports clubs, transfers etc.  This would have made tblAlloc more obviously a Fact table, with every Town transfer from one area to another, on a particular date, representing a single transaction that could be analysed.

Once again, I’m sorry to have caused frustration and I hope you’ll forgive me.

Pieter

Hi Pieter,

No apology necessary, I am just trying to illustrate that we're taking two steps forward and one step back (sometimes two) with each exchange and there's obviously more information that I'm not aware of that's driving your decision making. Without knowing the full picture I can't help you.

It's great that you provided a simple example of what you're trying to do, but by making it too simple you've left out important information which results in me providing you with answers that don't meet your needs. This is wasting time, both yours and mine.

I recommend you create a new example file that addresses EVERY scenario you're likely to come across and addresses all of your needs so that we can help you correctly the first time.

And take the Power Pivot course! This really is the best way, because it still seems to me that you don't really understand the purpose of the dim tables vs the fact tables.

In regards to having a primary key or not, it doesn't really matter as long as the dim table has a unique identifier for each town and area, this can be the name or an ID. If the AreaID and AreaName columns are both unique then the ID column is resulting in you storing data in Excel that you don't really need. Likewise for the TownID and TownName. 

Mynda

Hi Mynda,

Many thanks and I've taken everything in.  To be honest, although it seems that we were going round in circles (and we probably were) I was learning lots and had to make some irritating mistakes along the way.  I'm going to take your advice and take a few days to develop my thoughts and consider every scenario I'm likely to come across before coming back again.  Part of the problem was likely due to my trying to use Excel/PP as a full relational database rather than doing the number crunching that it's really good at!

I fully agree with what you say about using the unique Town or Area as the pimary key, though the "short code" I was using seemed "tidier".

I'll be back ... if that'sOK.

Pieter

No worries. Start a new thread when you come back.