Power BI Data Modelling Fact to Dim one to one relationship

[DebbieE]

I am trying to make sure I do my models right

I had a flat file of Events and I split this up into a Fact table and a dimension. this created a one to one relationship and I got the following 

The relationship is Cross filter direction Both and you cant change it against one to one.

I have been told that they should be single direction where possible.  I can change it to 1 to Many to get the single cross filter direction correct

But that wouldnt be true. it will always be one to one in this instance. Basically am I doing anything wrong here? and should I leave it as one to one Both Directions, or should it be changed to one to many so the Event can filter the metrics in the fact table single direction

Any help would be appreciated. im really trying to get my head round the modelling side

[amitchandak · Accepted solution]

@DebbieE , you can leave it one to one both directionss, if you do not planning join the dim event with other tables. if yes then prefer to have one to many

[kriscoupe · Accepted solution]

Hi Debbie,

You could bring the event title data into the fact table (since it is a one to one match) and not have a dim/fact setup. If you have a one-to-one match there is no real need for the dimension and filtering the fact table directly can be the best method. See this link below.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-one-to-one

Kris

[amitchandak · Accepted solution]

@DebbieE , you can leave it one to one both directionss, if you do not planning join the dim event with other tables. if yes then prefer to have one to many

[DebbieE]

When you say 'if you are not planning join the dim event with other tables' do you mean add a snowflake dimension joined to the event dim?

I am wanting to understand what the actual reason is why the one to one would only allow me to filter both directions considering I want to filter the measure table

[kriscoupe · Accepted solution]

Hi Debbie,

You could bring the event title data into the fact table (since it is a one to one match) and not have a dim/fact setup. If you have a one-to-one match there is no real need for the dimension and filtering the fact table directly can be the best method. See this link below.

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-one-to-one

Kris

[DebbieE]

I do agree, however, its simply to adhere to the modelling rules where you have your facts in one table and your descriptive information in another. 

thanks for the info. from this it seems like I have created a degenerate dimension.  I am guessing that there is nothing wrong for going with the metrics and descriptive data split because its mentioned here?

Otherwise you would be heading towards a flat file solution and I dont want to go there

[kriscoupe · Accepted solution]

Yes, there's nothing wrong with the setup you have implemented there if you want to keep your model understandable by seperating the factual items from the descriptive. And yes it's the degenerate dimension philosophy 🙂. Just be careful that when you add more tables to the model that the relationships do not make the model ambiguous.

Side note - Checked model I had with DAX Studio and whether I had a relationship set 1:1 with bi-directional or "fake" 1:M with one directional didn't seem to change the calculation logic or efficiency using simple measures.

[DebbieE]

I think I need to find myself a really good guide of creating star schemas for Power BI so I can brush up on it all again