Microsoft® Power BI™ best practices: Lessons learned - data engineering and Data Analysis Expressions (DAX)

by Garrett Becker

Do you struggle with data modeling and data sources?  Our team of certified Microsoft® Power BI™ analysts have been working with our customers to leverage the end-to-end platform to create powerful insights into their business. Through our work, we have identified several best practices or lessons learned we want to share to help you on your journey in building a data-driven culture through business intelligence.

The second article in our series is focused on data engineering and DAX.  The challenges of data engineering, transformation, and writing DAX calculations are real, but these eight best practices will help you elevate your work with this powerful tool.

1. Avoid using too many transformations in Power Query

• Streamline the number of transformations you use for greater efficiency when loading and refreshing datasets
• Helps reduce memory usage and aids in improving performance

2. Know when to use the "Direct Query" or "Import" when connecting to data sources

• "Direct Query" allows for an unlimited data size and the ability to schedule refreshes up to every 15 minutes from a single source
• "Import" is best for when you need data stored in the Power BI cache, it has the ability to pull from multiple sources and has a high-performance query engine, especially when you do not need to schedule refreshes as often
• Both are great options, but one is usually better dependent on the specific scenario

3. Create an "index" column when source data doesn't contain a unique identifier

• Sometimes it is necessary to create your own primary key for a dataset, if one is not already included
• This will help uniquely identify each record and help establish connections to other data sources
• This column also aids in removing duplicate rows if they were accidentally created during other transformations

4. Minimize the number of load queries by merging datasets

• You don't have to load all the datasets you're connecting to. Instead you can use the "Merge" data transformation tool to combine datasets and load fewer into the model.
• Speeds up processing and refresh times
• Allows you to keep only needed columns and decreases file size

5. Use 'Calculated Measures', not 'Calculated Columns', for overall data model performance

• Calculated Measures perform data operations only when called in a report and generally take up much less storage than Calculated Columns, which cache data in the file and can slow down data refreshes

6. Use "Calculate" function to quickly add in the "Filter" function as needed

• Instead of simply using the "Sum", "Average", or another function, wrap this function with the "Calculate" method to give yourself more flexibility and improve capabilities
• In the second parameter of "Calculate" you can add as many filters as you need, as long as your tables are connected in the data model

7. Create "Union" tables for greater customization

• With a simple "Union" function you can create custom tables using columns from multiple tables in a format you need
• This is great for creating a flexible trend axis with a slicer to select a cadence to view different scenarios, such as aggregations by week or by month
• You can use this new table in your data model and make connections as well

8. Make custom formatting measures using the “Switch” function

• By using the “Switch” function you can select specific table values to format, whether that’s the row’s background or font color
• Use hex codes in quotations to designate the desired color
• Use this measure in the conditional formatting section as “Field Value” for desired formatting effect

‍

We Can Help

Contact a team member to learn more about our data analytics services. Check out our first Power BI resource article about best practices in data modeling and source.