How not to spoil your Power BI data model – 7 critical rules

Do you want to create a database in Power BI, that:

is clear,
does not require unnecessarily complicated DAX functions,
is as fast as possible?

Then follow the basic rules. It´s not so hard and it will save you a lot of time.

1. Don´t use M:N relationships (=many to many)

Power BI offers 1:1, 1:N and M:N relationships.

1:1 usually don´t make a problem, but if you want to connect two tables 1:1, it may be better to merge them into one
1:N is a “default” choice in 99% situations
M:N is very different from the others. Don´t use it, when you don´t have a very serious reason (= never).
If you want to join two columns where values are repeated, it is much better to create a new dimension, insert it between them and make two 1: N relationships instead of one M: N relationship.

2. Use calendar dimensions

In short – do it every time you want to work with a column containing a calendar date. And you almost always want that 🙂

Because of:

Logic
Simpler DAX
Performance

3. Calculate measures – not columns

Why?

It´s easier to maintain, but mainly better in terms of performance.

Use new columns only if you want to use them for filtering, grouping… but even then try to create them in Power Query and not with DAX.

4. Do not load tables into data model, if you don´t need them for reports (even if you need them for another query in Power Pivot)

There is data that you need but, that you do not need in the data model. I mean queries, whose data is used only in other queries, but not themselves.

Example – you get exchange rates from web, and use them for foreign currency calculations. However, you don´t need the exchange list itself in data model.

So disable the load in Power Query. It will be better in terms of performance and especially in terms of clarity.

5. Dimensions should not have their dimensions – even if it means they have mor columns

In other words, don´t structure the database as a “snowflake”, but as a “star”.

This is because “going through” sessions slows down calculations and reduces performance.

Please note that this rule only applies to Power BI and other databases designed to read and display data. For other types of databases, “snowflake” may be much better.

6. Calculations, that need a lot of performance, should be done in Power Query, rather than in data model

Power Query is recalculated when loading, DAX is recalculated any time user interacts with report. So calculations in Power Query slow down loading, but don´t slow down working with report.

7. Keep correct data types

The right data types (usually set in Power Query) ensure that the data is not unnecessarily large (and therefore slow). Relationships may not work properly with the wrong data types.

A small note at the end

Yes, there are situations where it pays to break some of these rules. But it’s really exceptional and it only applies to quite complex models.

Do you want to create a database in Power BI, that:

is clear,
does not require unnecessarily complicated DAX functions,
is as fast as possible?

Then follow the basic rules. It´s not so hard and it will save you a lot of time.

1. Don´t use M:N relationships (=many to many)

Power BI offers 1:1, 1:N and M:N relationships.

1:1 usually don´t make a problem, but if you want to connect two tables 1:1, it may be better to merge them into one
1:N is a "default" choice in 99% situations
M:N is very different from the others. Don´t use it, when you don´t have a very serious reason (= never).
If you want to join two columns where values are repeated, it is much better to create a new dimension, insert it between them and make two 1: N relationships instead of one M: N relationship.

2. Use calendar dimensions

In short - do it every time you want to work with a column containing a calendar date. And you almost always want that :)

Because of:

Logic
Simpler DAX
Performance

3. Calculate measures - not columns

Why?

It´s easier to maintain, but mainly better in terms of performance.

Use new columns only if you want to use them for filtering, grouping... but even then try to create them in Power Query and not with DAX.

4. Do not load tables into data model, if you don´t need them for reports (even if you need them for another query in Power Pivot)

There is data that you need but, that you do not need in the data model. I mean queries, whose data is used only in other queries, but not themselves.

Example - you get exchange rates from web, and use them for foreign currency calculations. However, you don´t need the exchange list itself in data model.

So disable the load in Power Query. It will be better in terms of performance and especially in terms of clarity.

5. Dimensions should not have their dimensions - even if it means they have mor columns

In other words, don´t structure the database as a "snowflake", but as a "star".

This is because "going through" sessions slows down calculations and reduces performance.

Please note that this rule only applies to Power BI and other databases designed to read and display data. For other types of databases, "snowflake" may be much better.

6. Calculations, that need a lot of performance, should be done in Power Query, rather than in data model

Power Query is recalculated when loading, DAX is recalculated any time user interacts with report. So calculations in Power Query slow down loading, but don´t slow down working with report.

7. Keep correct data types

The right data types (usually set in Power Query) ensure that the data is not unnecessarily large (and therefore slow). Relationships may not work properly with the wrong data types.

A small note at the end

Yes, there are situations where it pays to break some of these rules. But it's really exceptional and it only applies to quite complex models.

Tutorials