Query diagnostics The query diagnostics tool that is built into Power Query allows us to see additional information related to each operation that Power Query performs. Many times, these Power Query operations will be data refreshes or when data is retrieved the first time; however,…
Optimizing with aggregations – Optimizing Model Performance
Optimizing with aggregations Any time you want to optimize your data model, in addition to reducing the data storage by removing unnecessary columns and rows, it’s also important to consider removing data by summarizing or using group by to reduce the number of rows and/or…
Optimizing relationships – Optimizing Model Performance
Optimizing relationships When report visuals are configured with fields from multiple queries or tables and expected results are not seen, then it’s possible there are issues with the relationships that have been set up (or autoconfigured by Power BI). When optimizing relationships, it’s important to…
When to use measures – Creating Measures Using DAX
When to use measures Measures are best used when values can or need to change based on the context. For example, the sum of revenue would change depending on the region we selected. Total revenue might be $1,000,000 with revenue across three different regions. Some…
Top N functions – Creating Measures Using DAX
Top N functions There is nothing in the RANKX function to limit the number of rows returned. We will see in this section how to limit our calculations to the top N. We will start with the easiest way: just asking. Q&Aing our way to…
Changing the default summarization – Creating Measures Using DAX
Changing the default summarization Power BI has many statistical functions. In fact, many of them are similar to their Excel counterparts. Many times, they can be accessed by right-clicking on the Values field in the Visualizations pane. In the dropdown, you can change the visual…
Creating semi-additive measures – Creating Measures Using DAX
Creating semi-additive measures Power BI has three different types of measures it can perform: additive, non-additive, and semi-additive. We will check each of them out in the following sections. Additive measures An additive measure, such as sum, aggregates over an element. Our gross profit calculations…
Optimizing data in the model – Optimizing Model Performance
Optimizing data in the model We’ve covered, in previous chapters, how Power BI supports directly connecting to a data store, such as connecting to an Enterprise Data Warehouse (EDW) that has billions of records and years of sales history. When configured with DirectQuery performance, Power…
Splitting numeric and text column data – Optimizing Model Performance
Splitting numeric and text column data In the VertiPaq engine, data is stored in a columnar data format. This means for a given query or table, each column is given a data type and all records for each column are stored independently from each other,…
Building complex measures with DAX – Creating Measures Using DAX
Building complex measures with DAX Power BI uses a programming language called DAX for creating calculated columns, calculated tables, and measures. This language can be used to easily create complex expressions to implement business rules. As we discussed in the previous chapter, measures are calculations…