Advanced Power BI: Advanced Measures and Querying

Name: Advanced Power BI: Advanced Measures and Querying
Author: Elizabeth Vergara

Perform advanced processes with measures and queries in Power BI

Preview the first lesson free — get full access to all 10 lessons.

Course: On-Demand

And this video, we will review query diagnostics, let's get started. Query diagnostics provides insight into the duration for each transformation step, refreshes and background events within the power query editor. We'll start by enabling query diagnostics by navigating to file, options and settings, options. And we'll go to diagnostics and under query diagnostics we just want to enable in report and query editor and it gives us notification that it may require running as an admin, we'll click Okay. Next, we'll navigate to the home tab. And under the query section, we'll click on transform data. Here within our data model, we have two different sales queries, one that has been designed to be efficient, and another that has been designed to be inefficient. The efficient query has just a fraction of the amount of applied steps as the inefficient query. For example, we have renamed all the columns together in one consolidated step and we've also changed the data type of these columns and one consolidated step. Whereas the inefficient query has renamed the column and change the data type, for each of these different columns, all in different steps. Now that we're familiar with these two examples let's capture the performance of each table using query diagnostics. We'll click on the tools tab, and we'll click on start diagnostics. For each table, we'll click on the refresh preview which we'll run through each of these applied steps on the table, and we'll navigate to the efficient query and also refresh that preview. And then we'll navigate back to tools and we'll stop diagnostics. When we're finished, what we'll see is a new diagnostics folder, which gives us multiple tables here. That gives us information about the performance of these two tables that we've just refreshed. The table that we're going to be most interested in is the diagnostics aggregated. Within the aggregated table, we can see where time is being spent on each query. So here we're given the name of the query or the table, the step that was applied, and if we scroll over, we see a start and end time and an exclusive duration and an exclusive duration percent. We're gonna navigate back to that specific table and right click on the diagnostics aggregated and we'll enable and load. So when we go to home and close and apply, we'll see that that table will be applied to our data model. So we can look at the exclusive duration percent, we'll navigate to the right side in the fields pan and expand the diagnostics aggregation. Here we're going to bring the query over to the values pane and the visualization will also bring step, as well as exclusive duration percent. We'll expand this visualization and we'll expand in the filters pane, the query filter. And we're gonna first click on the inefficient query. We see that 28% of its time was spent by changing the data types and renaming the columns. And if we navigate back over to the filters pane, we'll go to the sales query for diagnostic efficient. And we see here that only 7% of the time was spent changing the data types and renaming the columns as well as promoting headers. So right away, we can see that the efficient query is working more efficiently by consolidating those transformation steps, whereas the inefficient query had taken double even triple the amount of time. Query diagnostics are helpful when troubleshooting large complex data model queries, and can help pinpoint which steps are consuming the most resources. They will be helpful for you when designing robust data models. This concludes our session on query diagnostics. Thank you for watching.

Advanced Provider : Elizabeth Vergara 10 Lessons · 48m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

The more business gets done, the more data an organization can generate. Insights drawn from advanced data analysis open a world of opportunities for business owners and data processors to inform better business decisions. There’s also the potential for improved customer service.

Advanced data processing, formula validation, diagnostics, and query efficiency bring out the full utility of the Power BI system. You can use Power BI to perform advanced analysis of customer tracking and sales data. We’ll look at techniques for separating data through filters, including different regions.

In this Advanced Measures and Queries course for Power BI, you’ll learn advanced techniques for modeling data. We’ll cover how to perform query diagnostics. You’ll know how to work with Data Analysis Expression (DAX) functions, operators, and variables. We’ll also cover writing and formatting DAX queries.

You’ll take a look at DAX Studio and SQL Server Management Studio along with Dynamic Management Views (DMV) and DMV syntax. We’ll also cover how to export from DAX studio.

• Understand advanced query filters

• Perform advanced data analysis

• Explore advanced Power BI options

What You'll Learn

Apply advanced query filters to separate data across different regions
Perform advanced data analysis on customer tracking and sales data
Run query diagnostics to improve query efficiency
Work with Data Analysis Expression (DAX) functions, operators, and variables
Write and format DAX queries and statements
Use DAX Studio and SQL Server Management Studio, including DMV queries and syntax, and export from DAX Studio

Key Takeaways

Advanced data processing, formula validation, diagnostics, and query efficiency bring out the full utility of the Power BI system.
Power BI can be used to perform advanced analysis of customer tracking and sales data.
Filters can separate data, including across different regions.
The course covers DAX functions, operators, and variables along with writing and formatting DAX queries.
DAX Studio, SQL Server Management Studio, and Dynamic Management Views (DMV) support advanced querying and exporting from DAX Studio.

Frequently Asked Questions

What does this Power BI course cover?

It covers advanced techniques for modeling data, performing query diagnostics, working with DAX functions, operators, and variables, writing and formatting DAX queries, and using DAX Studio and SQL Server Management Studio along with Dynamic Management Views (DMV) and DMV syntax, including how to export from DAX Studio.

What skills will I gain from this course?

You will gain skills in Advanced Analytics, Data Analysis Expressions (DAX), and Power BI.

What are the main learning objectives?

The objectives are to understand advanced query filters, perform advanced data analysis, and explore advanced Power BI options.

What topics are taught in the lessons?

Lessons cover Query Diagnostics, DAX Functions, DAX Operators, DAX Variables, evaluating in DAX Studio or SSMS, defining DAX statements, DMV Queries, and formatting DAX code.

How can Power BI be applied to business data according to this course?

Power BI can be used to perform advanced analysis of customer tracking and sales data, using filters to separate data including by different regions, helping inform better business decisions and improve customer service.