CPE Today Logo

Power BI Best Practices: Working with Large Data Sets, Formatting, and DAX

Businesses of all sizes are generating data at an unprecedented rate. This data can be useful in understanding what is going on within the business and making better decisions. However, if this data isn’t properly understood and acted upon, it can quickly become overwhelming. That’s where Power BI comes in – a powerful suite of tools that can help you make sense of your data and take action on it.

In this blog post, we will discuss some best practices for working with large data sets in Power BI. We’ll also take a look at how to create more effective visualizations, dashboards, and reports with the tool. Finally, we’ll explore some of the advanced features available in Power BI that can make your reporting projects even more impactful.

Big Data, Big Impact

If you’re working with large data sets in Power BI, there are a few best practices to keep in mind. First, it’s important to understand the specific intention and use case of each visualization type. This will help you choose the right chart or graph for your data, and ensure that you’re conveying the right information.

Power BI has pre-built connections (aka apps or content packs) to many popular applications, such as Google Analytics, Zoho, and QuickBooks Online. “Point and click” integrations are the most popular kind. They don’t need you to develop any bespoke integrations. You’re ready to go once you’ve authenticated yourself. The Cloud app provider or Microsoft created and maintains these pre-made connectors. If you need to learn more about getting your data into Power Bi read our blog post.

Make your data look fantastic with formatting best practices

When creating visualizations, it’s important to use best practices for formatting. This will help ensure that your data is easy to understand and interpret. Just remember, to make formatting changes you must have edit permissions for the report. Some tips for good visualization formatting include:

  • Apply a theme to an entire report
  • Change the color of a single data point
  • Use Conditional formatting
  • Numeric value, field value
  • Customize colors used in the color scale
  • Use diverging color scales
  • Add color to table rows
Course Format

Securing your data through row level security (RLS)

Ever wanted to have the same dashboard present data to multiple types of users simultaneously? Consider Row Level Services (RLS)! Row level security lets you do just that. You can have different data displayed on the same dashboard for different users, or groups of users, by using filters and measures. This is a powerful feature that can be used to keep sensitive information hidden from some viewers, while still providing them with the insights they need.

When using RLS, you’ll need to create security roles in Power BI. To do this, go to the Admin portal and select ‘Security’. From here, you can create a new role and add users or groups to it. Then, you can set up the rules for what data each role can see.

However, it’s worth noting that this is only supported by Import and Direct Queries. If you try to publish a previously published Power BI Desktop report with Single Sign-on enabled, the “Test as Role/View as Role” option will fail and errors will be generated if you try to publish a previously published report from Power BI Desktop.

Help protect your data with Sensitivity Labels for Power BI.

Microsoft Information Protection sensitivity labels provide a simple way for your users to classify critical content in Power BI without compromising productivity or the ability to collaborate.  Sensitivity labels can be defined and applied to data in Power BI Desktop. You can then use these sensitivity labels when you share your reports with others.


Some features of using sensitivity labels include:

  • Customizable. You can create categories for different levels of sensitive content in your organization, such as Personal, Public, General, Confidential, and Highly Confidential.
  • Clear text. Since the label is in clear text, it’s easy for users to understand how to treat the content according to sensitivity label guidelines.
  • Persistent. After a sensitivity label has been applied to content, it accompanies that content when it is exported to Excel, PowerPoint and PDF files, downloaded to .pbix, or saved (in Desktop) and becomes the basis for applying and enforcing policies.

In order to use this feature, you must have an Office 365 subscription that includes the Information Protection feature. You also need to be a Power BI admin, or have the information protection administrator role. For more information on how to set up and use sensitivity labels, check out this link.

Using Data Analysis Expressions (DAX)

DAX is the formula language we use to modify data that is stored in a data model. It’s kind of like if Excel’s formula language and SQL had a baby together. There are elements of both languages present in DAX. You must use it to do calculations within Power BI. While it might seem complicated at first, you’ll master it in no time at all.

You can master the DAX syntax and the language in just a few hours. There are a lot of commonalities to normal Excel formulas, but also some important distinctions too. Once you have a handle on DAX, it opens up possibilities for more complex calculations and data manipulations that simply can’t be done in Excel.

For more information check out DAX Guide and Microsoft’s DAX guide

Course - Excel Best Practices

DAX Commonalities with Excel’s Formulas

Do you feel that you are somewhat fluent in Microsoft Excel and it’s formulas? Well, there are a lot of similarities between the two. A few of these include:

  • Both are expression languages
  • Same names and functionality
  • Use similar vector and array lookup functions
  • Both start with equal signs

However, some of the differences between DAX and Excel Formulas include:

  • DAX functions take different types of inputs, therefore, return different data types.
  • DAX functions cannot be used in Excel formulas or vice versa
  • Excel uses cell references or a range of cells as a reference; DAX uses column or table
  • DAX displays a datetime data type that does not exist in Excel.
  • Only DAX has a function that returns a table
  • DAX lookup functions require tables have established relationships
  • DAX must have only one type of data per column
  • DAX Formulas can have filters added to them

For more information on DAX functions, check out This article.

Using Calculated Measures & Columns

What is a Calculated Column?

A Calculated Column is a column that you add to a Power BI table in order to calculate values for every row in that table. A calculated column returns back several results, one for every row. The calculation is performed over all the rows of that table, and the result is added as an extra column to the end of your existing data. It also increases your file size and uses a lot of computing space, so it’s best to use this for data that doesn’t change very often.

Click Here to learn how to created a calculated column.

Using Quick Measures

Quick measures are pre-built measures that do not require DAX syntax to build. there are six types: Aggregate per Category, Filters, Time Intelligence, Totals, Mathematical Operations, and Text. However, these are only available if you have the rights to modify the model.

How to use Quick Measures

You simply Drag and Drop the measure from the Measures pane to your worksheet, or use Get Data > Power BI Datasets to import a pre-built Quick Measure.

Currently, there are about 50 different quick measures that you can use right out of the box. And more are being added all the time. You can find them by looking  Here.

Course - Power BI

These are just a few of the many features that you can use in Power BI.  There are a number of additional resources that can be helpful when working with large data sets and developing reports in Power BI. We will go through some of these in-depth in other courses and blog posts.

Join Our Newsletter

Newsletter - Sidebar
Consent Acknowledgement