data modelling

BOBALENIS — Wed, 21 Apr 2021 08:59:09 GMT

I have a fact table where each row contains different data at a local authority level. There are therefore many rows regarding the same local authority. I then have a dim/lookup table where I list a unique list of local authorities, so I can use this to slice my data. All great so far. Now I have a table that lists the local authority in one colum and in the next it lists all the other local authorities that are considered statistcally similar.( stat neighbours, see below).

e.g

LA Name Stat Name

Waltham. Eastbourn

Waltham. Hasting

Waltham. Borough

Wirral Eastbourn

Wirral Leeds

etc

OUTCOME: I would like to select a LA Name and the fact table is filtered according to the stat Names so I can show this data as a comparison for the LA Name - you might like to consider them as benchmarks, which are there to help the user undertsand if the selected LA Name is doing well or not

PROBLEMS: For each Local Authority you have many other local authorties that are stats neighbouts (up to 10 ish) Also, each local authority could be in one or more different stat neighbour groups.

So using the example above the local authority 'Eastbourne' is a stat meighbour of both 'Waltham'. and 'Wirall' above.

Solution 1. is to create a many to many relationship between the stat neighbour LU/Dim table and the fact table. NOT IDEAL as you would prefer a 1 to many relationship here

Solution 2. You can use the LA dim/LU to create a bridge table between the stat neighbout and the fact table- this has limited success and you need to add bi directional flow- NOT IDEAL as you would ideally have the flow in one direction only

Is there a nicer solution that I am missing?

THANKS SO MUCH

Re: data modelling

amitchandak — Wed, 21 Apr 2021 09:17:06 GMT

BOBALENIS , Based on what I got. I think multiple join with authority table to fact. with one relation inactive and activate that using userelationship

refer :https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Re: data modelling

BOBALENIS — Wed, 21 Apr 2021 09:22:34 GMT

Morning and wow- thank you so much for replying so swiftly.

I am not sure I fully understand your idea but do you mean something like this? Thanks again

topic Re: data modelling in Desktop

data modelling

Re: data modelling

Re: data modelling