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 8 best practices will help you elevate your work with this powerful tool.
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
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
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
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
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
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
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
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.
