DAX HANDBOOK
5. Data Model

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

The backbone of every good and fast DAX calculation is a well-defined data model.

What is a Data Model?

In simple terms, the data model consists of all the tables in your Power BI/Excel/analysis services database that are connected through relationships. The definition also includes other, non-related tables, as well as other objects in the model like Measures, calculation groups etc., but for now, we will stick to the simple definition.

Why Data Model?

If you come from an Excel environment, you might think why would you need a data model, when you can have it all in one single, big fact table? There are several reasons that go in favor of preparing a proper data model prior to any calculation or data analysis. We will mention a few of them:

  1. Easier understanding of the data
    • If you have multiple columns that have the same name (e.g. Address which can be linked both to customer, buyer, employee, or other parts of the data model), it’s much easier to spot the correct one if it is a part of the dimension (e.g. Customer[Address]), rather then denormalized in a single table with prefix.
  2. Handling data with different granularities
    • Imagine having actual data on an SKU level with daily sales. You need to compare it to the forecast values that are on the monthly/brand level. It would be a daunting task to incorporate both tables into a single fact table, due to their obvious differences in granularities. In the case of a data model, you can prepare the dimension that can simultaneously filter both actuals and forecast data.
  3. Simpler and faster DAX code
    • You can forget about using time-intelligence functions without a date dimension linked with a fact table
    • Also, most complex DAX code can be simplified with the help of a well-defined data model
  4. More flexibility
    • With additional tables, you can introduce parameters, calculation groups, field parameters, and other objects that cannot be a part of a single table.
  5. Better security
    • Row-level-security is best defined on dimension levels that filter necessary table/s.

There are many more advantages to having a proper data model than a single table that we haven’t thought to mention. In the following articles we will explain optimal data model structures, often referred to as a star scheme.

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top