This two-day workshop is a complete course about optimizing queries and measures in the DAX language. DAX is the native language of Power Pivot for Excel, Power BI Desktop and SSAS Tabular models in Microsoft SQL Server Analysis Services. This course is aimed at BI professionals, Power BI and Excel users who already know the DAX language and want to learn how to optimize DAX formulas and models for improving performance.

The goal of the course is to enable you to find bottlenecks in DAX calculations using tools such as Profiler and DAX Studio, providing the knowledge to improve the performance by using different DAX formulas or by changing the data model, when required. All the attendees will receive a copy of the new book The Definitive Guide to DAX.

Prerequisite: Attendees need to have a good knowledge of the DAX Language, and a previous experience using either Power Pivot for Excel, or Power BI Desktop, or Analysis Services Tabular. You might acquire this knowledge by participating to the Mastering DAX Workshop, or having equivalent real-world experience.

Topics covered:

xVelocity engine internals

Hardware considerations

Monitoring Tabular server

Reading Profiler Traces

Understanding Formula & Storage Engines

Reading query plans with DAX Studio

Examples of DAX optimizations

Optimizing Data Models and Relationships Usage

The course includes hands-on labs: you will make practice using your own notebook.

Each attendee must bring his/her laptop for exercises. We will provide the configuration required, or in alternative it will be possible to connect to a virtual machine on the cloud using Remote Deskop and the Internet connection available in the training room.

Course time

09:00am » 05:00pm

Course location

CCTvenues Barbican
135-137 Aldersgate St. - EC1A 4JA
London, UK » Get directions

Special Bundle Offer

Don't miss another great course in a convenient time frame! Register to both courses and obtain a special bundle price:

Learn to master DAX query language and Tabular patterns to boost your Power BI, Excel Power Pivot or Analysis Services solutions.

Self-Assessment for Prerequisites to attend the course

Time limit: 0

Quiz-summary

0 of 10 questions completed

Questions:

1

2

3

4

5

6

7

8

9

10

Information

This self-assessment has the goal to check whether you have the right prerequisites to attend the Optimizing DAX Workshop in a proficient way. The course is an intensive one and in order to get the maximum from the 2 days, it is important to know the DAX language very well. In particular, the test check your knowledge of evaluation context, context transition, and DAX functions that are commonly used.

If you do not have a good confidence of DAX, you might be unable to get the most from the Optimizing course. Depending on your final score, the final message will give you a suggestion to prepare for the Optimizing DAX Workshop, such as topics to study, books, videos, and other courses.
In any case, you will be your first judge, especially if you do not feel yourself confident answering to the questions of this test. We suggest you to answer “I don’t know, I’m not sure” when you are not able to choose an answer.

Read the following questions carefully and find the correct answer from the options provided. We do not provide a suggested time time to answer each question. You should look at the answers and select the one you believe is correct – this is more important than speed. Take the time you need to get the answer correct, but don’t waste time once you have the answer you will choose. Some questions are just more difficult, and will naturally take longer to answer.

You can access the Internet and other documentation during the test if you like. The goal is to measure your understanding of DAX concepts that are fundamental in performance analysis and optimization. It is not a goal of the test to measure your knowledge of the syntax of DAX measures, so it is ok if you read the documentation of a function to check the position of a certain argument.

Remember: don’t cheat yourself!

You can fill the form in an Anonymous way, or you can leave us your email. We will not disclose the result of your test, guaranteed!

You must specify a text.

You must specify an email address.

You have already completed the quiz before. Hence you can not start it again.

Quiz is loading...

You must sign in or sign up to start the quiz.

You have to finish following quiz, to start this quiz:

Results

Time has elapsed

000

Categories

Not categorized0%

Sorry, but Optimizing DAX Workshop is too advanced for you.

You should start studying DAX from the ground-up. Your score is below 20%, so attending the Optimizing DAX would not be useful to you by now.
You can start learning DAX with one of the Power Pivot video courses for Excel 2010/2013: you can choose live or recorded video. After making practice with DAX, you can attend the Mastering DAX and then the Optimizing DAX courses.

You have a basic knowledge of DAX, we strongly suggest to improve your DAX skills before attending the Optimizing DAX Workshop.

Your score is below 75%, which is the suggested level to attend the Optimizing DAX course.
You should attend the Mastering DAX Workshop before attending the Optimizing DAX course,

You have a medium knowledge of DAX, but we suggest to improve your DAX skills before attending the Optimizing DAX Workshop.

Your score is below 75%, which is the suggested level to attend the Optimizing DAX course.
You should consider attending the Mastering DAX Workshop before attending the Optimizing DAX course.

You have a good knowledge of DAX and attending the Optimizing DAX will be useful for you!

Your score is above 75%, which is the suggested level to attend the Optimizing DAX course.
Just review the topics where you were not sure in your answers, so you will arrive at the workshop in good shape!

You are a DAX master and you will enjoy attending the Optimzing DAX.

It seems you know the DAX language very well: it is easy to predict that you will ask the best questions during the workshop and you will have fun optimizing your DAX measures.

1

2

3

4

5

6

7

8

9

10

Answered

Review

Question 1 of 10

1. Question

Consider the following expressions:

Total Sales of Red Products:= CALCULATE ( [SalesAmount], Product[Color] = "Red" )
Total Sales of SQLBI Branded Products:= CALCULATE ( [SalesAmount], Product[Brand] = "SQLBI" )

Which of the following DAX formulas will return all products with “SQLBI” brand AND/OR all products with the color Red while not altering any other filters in a pivot table?
In other words, how do you write an OR statement between two columns with CALCULATE?

What is the value of the measure “Result” when it is otherwise unfiltered?

Question 4 of 10

4. Question

Consider the scenario where you have the following data model:

There is a Sales table, a Product table and a Customer table.

The customer table includes information about which City each customer lives.

There is an active relationship from the Sales table to both the Product table and the Customer table.

You have a pivot table in a spreadsheet that has a slicer on Products.

Below are a number of possible measures to calculate “Total number of Cities”. Which of these measures will return the total number of cities where there is at least one customer for sales of the products filtered by the slicer?

Question 5 of 10

5. Question

There are 6 DAX expressions listed below.
Three of these expressions will return the same result if used in a measure filtered by different colors, and other four will return different results.
Select the two items below that will return the same result regardless of the color selected.

The following measure is intended to compute the Quantity On Hand:

Quantity on Hand :=
CALCULATE (
SUM ( Transaction[Quantity] ),
FILTER ( Date, Date[DateKey] <= MAX ( Date[DateKey] ) )
)

When this formula is used, it reports wrong numbers.
Which of the following filter conditions should be used to correct the formula so that it works as intended?

Question 8 of 10

8. Question

Which of the following correctly explains the difference between ISFILTERED and ISCROSSFILTERED?

Question 9 of 10

9. Question

Consider the following data model:

The Products table has 10 rows

The Sales table has 1000 rows (including 20 rows that have a product code that does not have a matching value in the Products table)

There is an active relationship between Products and Sales

You have the following measures in the data model:

What is the value of the measure “Result” when it is otherwise unfiltered?

Question 10 of 10

10. Question

The following query will return a table containing all colors in the first column and the total sales for each color in the second column. It will return blank in the second column if there are no sales for that color.

How can you modify the query to exclude colors from the table that do not have any transaction in sales table? I.e. only return colors where there are corresponding rows in sales table.