Power BI Best Practices

There are many best practices for Power BI. I know these things so intuitively now that it is very easy to forget how I incrementally learned these things along the journey. Most of these things are not “right” vs “wrong” — they are more often simply “better”. I thought there would be value in producing a page that clearly outlines the important best practices as a learning reference for everyone. In all cases, I have outlined why it is a best practice to help the understanding.

 

We should group the measures in the specific Folders

• We can create separate measures in a different folder group
• It helps us to distinguish between different types of measure as in the above example we can see tha we can categorize the measure

Naming Conventions

 

Naming your Columns and Measures

• Always write a Column in the format TableName[Column Name]
• Always write a Measure in the format [Measure Name]

This is the foundation of all the DAX formulas you will write. Both columns and measures use the same square bracket syntax. It is technically possible to write measures and columns both including the table name as follows.

• TableName[Column Name]
• TableName[Measure Name]

These 2 examples above do not cause a problem because their names make it easy to know what they are, but if you always place the table name at the front of every measure and every column, then it will be impossible to tell them apart by reading the formula. Take the following example

• TableName[Total Sales]

Is the above a column or a measure? It is impossible to tell unless you are using the best practice naming convention.

 

Give Tables a Single Noun Name

• Don’t just accept the table name from your source system. Preferably give the table a single word noun description/name.

Many BI data sources will have long table names like fctSalesTransactionsHistory or dimCustomerMasterFile. This is a common practice in IT, you can name your own table which makes it to understand better.

 

Don’t Overly Abbreviate Business Terms

• Give your tables, columns and measures descriptive business names without overly short abbreviations.

Firstly you should use the language and abbreviations that are commonly used in your organisation. So if “Year to Date” is commonly abbreviated to YTD, then for sure you can use this abbreviation in your measure names, e.g. [Total Sales YTD]. However if you develop a new measure called [Total Sales Last Rolling Quarter] and this is not a common concept across the organisation, then you are just making it hard for yourself if you call your measure [Ttl Sales LRQ]. You will simply have people calling you asking what it means.

 

Shape with ‘M’ in Power Query, Model with DAX in Power BI

 

Power Query (‘M’) and DAX were built to do 2 completely different tasks. Power Query is built for cleansing and shaping while DAX is built for modelling and reporting. It is possible that you can shape your data with DAX (e.g. you can write calculated columns, you can add calculated tables, etc.). But just because you can do these things with DAX, doesn’t mean you should. For example, it is possible to write letters to people using Excel, but Word is a much better tool for this task (I knew someone that once did that!).

 

A Star Schema is Optimal

• Power BI is optimised to use a Star Schema table structure 

 

I am not saying this is the only layout that will work, or that other designs will always be slow. I am saying that if you start out thinking about a star schema and aim to build that design you will be well underway to success. Two key things you should know.

• Don’t just bring in what is in your source transactional database — that would likely put you into a world of pain.
• There is no need to create a lookup/dimension table just for the sake of it. If your sales table has a customer name and you don’t care about anything else about the customer (e.g. city, state etc.), then there is no need to create a lookup table just for the sake of creating a star schema. If you have 2 or more columns relating to the same object in your data table, then it is time to consider a lookup table. 

Only Load the Data You Need

• Load all the data you need, and nothing you don’t need.

If you have data (particularly in extra columns) you don’t need to load, then don’t load it. Loading data you don’t need will make your workbooks bigger and slower than they need to be. In the old world of Excel, we all used to ask IT to “give me everything” because it was too hard to go back and add the missing columns of data later. This is no longer the case — it is very easy to change your data load query to add in a column you are missing. So bring in all of what you need and nothing you don’t. If you need anything else later, then go and get it later. Focus mainly on your large data tables — the lookup/dimension tables tend to be smaller and hence are generally less of an issue (not always).