Module 2: Using Data Analysis Expressions (DAX) in Power BI

by
Contents hide
1 INTRODUCTION – Using Data Analysis Expressions (DAX) in Power BI
2 SELF-REVIEW: ADDING A CALCULATED TABLE AND COLUMN
3 KNOWLEDGE CHECK: USING DATA ANALYSIS EXPRESSIONS (DAX) IN POWER BI
4 KNOWLEDGE CHECK: INTRODUCTION TO MEASURES
5 SELF-REVIEW: ADDING A MEASURE
6 KNOWLEDGE CHECK: WORKING WITH MEASURES
7 SELF-REVIEW: ADDING A ROLE-PLAYING DIMENSION
8 KNOWLEDGE CHECK: DAX AND TABLE RELATIONSHIPS
9 SELF-REVIEW: USING TIME INTELLIGENCE TO COMPARE TO PREVIOUS YEAR
10 KNOWLEDGE CHECK: TIME INTELLIGENCE CALCULATIONS IN DAX
11 MODULE QUIZ: USING DAX IN POWER BI
12 CONCLUSION – Using Data Analysis Expressions (DAX) in Power BI
Spread the love

INTRODUCTION – Using Data Analysis Expressions (DAX) in Power BI

 

This module offers a thorough introduction to the Data Analysis Expressions (DAX) language, guiding learners through its complex syntax and diverse applications. By exploring various business use cases, the module demonstrates how DAX can be applied to solve real-world problems and generate valuable insights.

Additionally, the module effectively connects DAX concepts with previous lessons on database tables, highlighting their practical use and significance. A key focus is placed on time intelligence, illustrating how DAX can be used to perform advanced time-based calculations and analyses. Overall, this comprehensive module is designed to provide learners with a strong understanding of DAX, empowering them to apply it effectively in different business contexts.

Learning Objectives:

  • Write DAX calculations to create elements and perform analysis in Power BI.
  • Create calculated columns and measures within a model.
  • Perform essential time intelligence calculations in DAX.

SELF-REVIEW: ADDING A CALCULATED TABLE AND COLUMN

1. What does the DAX QUARTER function do when executed in the Date table? 

  • It returns a year’s quarter as a number extracted from the Date column. (CORRECT)
  • It creates a column from the Year column of the Date table.
  • It converts the selected Date column data into a year’s quarter.

Correct! The QUARTER function returns a quarter of the year as a number from 1 (January – March) to 4 (October to December).

2. You have created a new table called Yearly Sales by Color. What is the total number of columns in the table?

  • 11 (CORRECT)
  • 12
  • 9

That’s correct! The original Sales table contains 9 columns, and you created a new table by extracting two new columns.

3. What role did the RELATED function perform when you created the Yearly Sales calculated table? Select all that apply.

  • It referenced a column from the date table within the data model to retrieve the year information. (CORRECT)
  • It referenced a column from the Sales table to retrieve the year information. 
  • It referenced a column from an unconnected table within the data model to retrieve the year information.
  • It referenced a column from the product table within the data model to retrieve the color information. (CORRECT)

That’s correct! The RELATED function extracts the required information from the Date table to perform the analysis.

That’s correct! The RELATED function references the Color column from the Product table within the data model.

4. Identify the components of a data analysis expression. Select all that apply.

  • Data types (CORRECT)
  • Syntax (CORRECT)
  • Operators (CORRECT)
  • Column and table references (CORRECT)

Absolutely! Understanding the different data types in DAX is crucial since the language works with specific types such as numeric, text, date, and boolean. Knowing how to handle these types helps ensure your formulas are accurate.

The correct syntax is essential for writing effective DAX expressions. Mistakes in syntax can lead to errors or unexpected results in your analysis.

Operators in DAX are fundamental for performing calculations. These include arithmetic operators (e.g., +, -, *, /), comparison operators (e.g., =, <>, >, <), and logical operators (e.g., AND, OR, NOT), all of which are used to manipulate data.

Finally, correctly referencing columns and tables is key to structuring your formulas and ensuring they function as intended. Improper references can lead to issues with results or performance.

Feel free to ask if you’d like more clarification or examples on any of these topics!

5. True or False: Filter context can only be applied to a calculation via writing a DAX expression.

  • True
  • False (CORRECT)

Correct. There are two ways to apply filter context; via writing a DAX expression and through user interface in report visuals of Power BI desktop.

6. What is the purpose of using variables in DAX?

  • A contact list
  • A network diagram
  • An incident response plan (CORRECT)
  • A security policy

The definition of emergency response plans draws lines on how an organization should act when a ransomware attack occurs.

7. Which of the following statements regarding calculated tables in Power BI and DAX are true? Select all that apply.

  • Calculated tables can only be created from a single data source.
  • Changes to the original data source will always be reflected in the cloned calculated tables.
  • Calculated tables combine data from different sources to facilitate deeper data analysis. (CORRECT)
  • Calculated tables are created within Power BI using DAX expressions. (CORRECT)

Correct! Calculated tables can consolidate data from different sources, facilitating comprehensive and deep data analysis.

Correct! Calculated tables are indeed created within Power BI using DAX expressions.

8. In Microsoft Power BI, what is the function of a calculated column?

  • It visualizes data in various formats such as charts and graphs.
  • It sorts and filters data based on user-defined criteria.
  • It performs calculations on data using a DAX formula. (CORRECT)

Correct! A calculated column performs calculations on other data in the table using a DAX formula, generating new data that can be used in reports and visuals.

KNOWLEDGE CHECK: USING DATA ANALYSIS EXPRESSIONS (DAX) IN POWER BI

1. You are a data analyst working with a large sales dataset for an e-commerce company. You are employing Power BI as an analytical tool. In which of the following scenarios would it be beneficial to use DAX functions? Select all that apply.

  • Creating a column that flags high-value customers. (CORRECT)
  • Calculating the average sales per region. (CORRECT)
  • Filtering the data to display only this year’s sales. (CORRECT)
  • Importing a new sales dataset.

Correct! DAX can create new calculated columns, such as a column that flags high-value customers.

Correct! DAX can be used to perform complex calculations like average sales per region.

Correct! DAX can filter data based on certain conditions, such as sales from a specific year.

2. True or False: You can create a calculated table in DAX to generate a table based on the defined expression.

  • True (CORRECT)
  • False

That’s correct! A calculated table is a table that’s added to an existing data model in Power BI Desktop. The rows in the table are calculated from an expression (DAX formula) that you provide when you create the table.

3. What is the purpose of the RELATED function in DAX?

  • To filter data based on specific criteria.
  • To retrieve data from a related table. (CORRECT)
  • To establish relationships between tables.

That’s correct! The RELATED function extracts data from the related table within the data model.

4. Why should you clone a table in Power BI for use as a calculated table?

  • To reduce the size of your data model.
  • To keep a backup of your original dataset.
  • To perform calculations that are not possible on the original dataset. (CORRECT)

That’s correct! The objective of cloning a table is to keep the original dataset intact.

5. Regarding DAX, what does the term “evaluation context” refer to?

  • The visualization and charts created by DAX formulas.
  • The set of filters and context that influence DAX calculations. (CORRECT)
  • The connection between Power BI and external data sources.

That’s correct! Evaluation context is the set of filters applied to a DAX expression that determines the subset of data on which DAX operates.

KNOWLEDGE CHECK: INTRODUCTION TO MEASURES

1. Which of the following statements about the measures is correct?

  • Measures can reference columns directly.
  • Measures store values in the model.
  • Measures can reference other measures directly. (CORRECT)

That’s correct! You can refer to measures within the measures to compute a specific calculation. 

2. Which DAX function can summarize the number of entries in a table?

  • SUM
  • COUNTROWS (CORRECT)
  • AVERAGE

That’s correct! The COUNTROWS function can summarize a table by returning the number of rows.

3. True or False: In DAX, a semi-additive measure can accumulate values over some dimension but typically not over time.

  • True (CORRECT)
  • False

That’s correct! A semi-additive measure can add value over some dimension but not some others, a typical example is time.

4. Which of the following DAX functions modifies the evaluation context of a specific calculation? Select all that apply.

  • CALCULATETABLE (CORRECT)
  • MEDIAN
  • DIVIDE
  • CALCULATE (CORRECT)

CALCULATE is one of the most powerful functions in DAX, as it allows you to modify the evaluation context of a calculation. It changes the context in which the data is evaluated by applying filters or removing existing ones. This flexibility helps you create dynamic measures and calculations.

CALCULATETABLE is a companion function to CALCULATE, but instead of returning a scalar value, it returns a table. This allows you to alter the context of a table expression, enabling more complex table manipulations while still applying context transitions, filters, and calculations.

5. You want to calculate the total revenue for each product category in an Adventure Works sales dataset, but only for products over $1000. You can use the ___________ function to narrow down the products with a specified amount. You can then use the ____________ function to compute the final measure.

  • AVERAGEX, CALCULATE
  • RELATED, FILTER
  • FILTER, CALCULATE (CORRECT)

That’s correct! The FILTER function narrows down the product with a specified sales amount. The CALCULATE function computes the final measure with appropriate filter conditions.

6. Why are measures used in Power BI?

  • To filter and categorize data.
  • To perform calculations and aggregations. (CORRECT)
  • To store descriptive attributes.

That’s correct! The main objective of creating measures in Power BI is to perform calculations and aggregations for deeper analysis.

7. Which of the following business dimensions can Adventure Works aggregate using additive measures?

Select all that apply.

  • Total revenue (CORRECT)
  • Time (CORRECT)
  • Product category (CORRECT)
  • Region (CORRECT)

You’re absolutely right: additive measures are designed to aggregate data across any business dimension. This means you can use them to sum, average, or perform similar operations over different aspects of your data, such as sales, quantity, or revenue, across various dimensions like time, location, or product categories.

These measures are typically used in business intelligence to provide a consolidated view of the data, allowing users to analyze trends and make informed decisions.

8. What is the COUNT function in DAX used for?

  • To count the number of non-blank cells in a column.
  • To count the number of rows in a column. (CORRECT)
  • To count the number of distinct values in a column.

Correct! The COUNT function counts the number of rows in a column or a table.

9. True or False: The context in which your calculations are executed can give rise to variations in data.

  • True (CORRECT)
  • False

Correct! Custom calculations are context-sensitive. Understanding the influence of context is important because it can result in variations in your calculations.

SELF-REVIEW: ADDING A MEASURE

1. In the exercise, you created a quick measure running total. Which of the following fields did you add as your base value?

  • The Order date column from the Sales table.
  • The Year column from the Date table.
  • The Total Sales column from the Sales table. (CORRECT)

That’s correct. The Total Sales column is the field for which you need the running total.

2. What methods could you have used to access the Quick Measure feature in Power BI? Select all that apply.

  • Access the feature from the drop-down menu using the ellipses under the Data pane. (CORRECT)
  • Select the New measure option from the Model view of Power BI desktop.
  • Select the Quick Measure option from the Power BI Home tab. (CORRECT)
  • Access the feature through Power Query editor.

That’s correct! The Quick measure option can also be accessed from the drop-down accessed from the ellipses on any field from the Data pane of Power BI desktop.

That’s correct. The Quick Measure option on the Home tab provides a user-friendly way to create measures in Power BI.

3. True or False: You can use the SUM function instead of SUMX to calculate the Total Sales measure in DAX.

  • True
  • False (CORRECT)

That’s correct! The SUMX function iterates over a table and calculates the sum of each row. This is the correct method to compute the total sales for each row of the Sales table.

4. What is the benefit of creating quick measures in Power BI?

  • To accelerate the creation of calculations. (CORRECT)
  • To facilitate the writing of DAX expressions.
  • To eliminate the need for data modeling.

Correct! Because of the user-friendly interface, quick measures accelerate the process of creating calculations needed for analysis.

5. True or False: When creating a measure in DAX, you need to establish active relationships between the tables within your dataset to make sure you obtain accurate results for your analysis.

  • True (CORRECT)
  • False

That’s correct. Relationships between various dataset tables are essential in computing accurate calculations and creating dynamic measures in Power BI.

KNOWLEDGE CHECK: WORKING WITH MEASURES

1. How can measures help with data analysis in Power BI?

  • By creating new tables and relationships between data tables in your models.
  • By performing calculations and aggregations for data visualization and analysis. (CORRECT)
  • By combining data from various external data sources.

That’s correct! Measures are used to perform calculations of the data for custom visualization and reporting. 

2. In which scenario might the CROSSFILTER function be useful?

  • When establishing relationships within the tables.
  • When applying complex calculations within a measure.
  • When creating visualizations with custom filtering behavior. (CORRECT)

That’s correct! CROSSFILTER only changes the filter direction between the connected table for the created measure. So, it helps in creating custom visualizations.

3. True or False: Measures in Power BI are calculated columns that store results based on a specific DAX expression.

  • True
  • False (CORRECT)

That’s correct! Measures are dynamic calculations computed during a query. They do not take up storage.

4. Which of the following are the DAX modifier functions? Select all that apply.

  • CROSSFILTER (CORRECT)
  • KEEPFILTERS (CORRECT
  • SUMX
  • FILTER

CROSSFILTER is a powerful DAX function that allows you to modify the filter direction between two related tables. By adjusting the filter direction, you can control how the filter propagates through relationships in your model. This can be especially useful when you want to customize the filtering behavior in scenarios involving bidirectional relationships or when working with specific data interactions between tables.

KEEPFILTERS is used to apply additional filters without removing existing filters on the same columns. It preserves any filters already in place, ensuring that the new filter does not override them but rather adds to the filter context. This helps maintain the integrity of other filters while applying additional constraints.

5. You are analyzing a data model comprised of a Customers and an Orders tables. You want to analyze the number of customer orders from a specific region. Which of the following functions, combined with CROSSFILTER, can you use to generate these insights?

  • CALCULATE (CORRECT)
  • SUMMARIZE
  • COUNTROWS

That’s correct! You can use CALCULATE with CROSSFILTER to modify the filter context and calculate the orders by the customers. The CROSSFILTER function also ensures that the desired filter direction is applied to the region.

6. True or False: The CROSSFILTER function can only be used with a DAX function that uses a filter as an argument.

  • True (CORRECT)
  • False

Correct! CROSSFILTER can only be used in functions that use a filter as an argument, such as CALCULATE.

7. Why might you want to override the default filter context?

  • To create new relationships between tables in the model.
  • To create measures that do not aggregate or average data.
  • To create measures that behave according to your intent. (CORRECT)

Correct! You can override the default context to create measures that behave according to your intentions, regardless of what the user selects.

SELF-REVIEW: ADDING A ROLE-PLAYING DIMENSION

1. You added the date table as a role-playing dimension to analyze the August sales. What alternative method could you have used to perform the same analysis in Power BI?

  • Create a calculated column in the Date table.
  • Clone the B table in DAX and name it Shipping date table. (CORRECT)
  • Create a calculated table by extracting data from the Sales and Date tables.

That’s correct! You can clone the date table using the ALL function and rename it Shipping date.

2. What was the purpose of the USERELATIONSHIP function in creating the August sales by shipping date measure for Adventure Works? 

  • To establish custom relationships between the Sales and the Date table. (CORRECT)
  • To define default relationships between the Sales and Date table.
  • To filter the data based on specific conditions.

That’s correct! The USERELATIONSHIP function overrides the default relationship between the tables to compute the desired calculation.

3. True or False: In your Adventure Works data model, the default relationship between the Sales and Date tables is based on the order date. Could you make this relationship inactive? You can convert the default relationship between the sales and date table to inactive relationship in Power BI for a specific calculation?

  • True (CORRECT)
  • False

That’s correct! You can make any relationship active or inactive from the Edit relationship dialog box of Power BI.

4. Adventure Works’ data model includes a Location dimension table. This table is used twice. Once as the origin location, and again as the destination location. This design allows the company to analyze which of the following dimensions effectively?

  • Both Sales and Shipping data based on different perspectives without creating redundant structures. (CORRECT)
  • Sales data related to origin location without creating redundant structures in the model.
  • Sales data based on destination location without creating redundant structures in the model.

Correct! By using the Location dimension table as origin location and destination location, the company can analyze Sales and Shipping data without creating redundant dimension tables.

5. Which of the following best represents the syntax of the USERELATIONSHIP function in DAX?

  • USERELATIONSHIP 
  • ( TableName[ColumnName], TableName[ColumnName]) (CORRECT)
  • USERELATIONSHIP 
  • (ColumnName, TableName[ColumnName])
  • USERELATIONSHIP 
  • (TableName, ColumnName)

Correct! Two columns from two related tables are required for the USERELATIONSHIP function to execute correctly.

6. True or False: The USERELATIONSHIP function in DAX establishes custom relationships between the tables in a data model, even if a relationship does not already exist between the tables.

  • True
  • False (CORRECT)

The USERELATIONSHIP function is used when you have multiple relationships between two tables, but you want to use a specific inactive relationship for a particular calculation.

KNOWLEDGE CHECK: DAX AND TABLE RELATIONSHIPS

1. How can you use an inactive relationship in a single measure?

  • Use the USERELATIONSHIP function in DAX. (CORRECT)
  • Change the active relationship in the modeling view of Power BI.
  • Delete one relationship from the modeling tab of Power BI.

That’s correct! The USERELATIONSHIP function overrides the relationship for just the measure under consideration.

2. How does the USERELATIONSHIP function impact the relationship between tables in a data model?

  • It overrides the existing relationship state between tables. (CORRECT)
  • It automatically generates relationships based on the column names.
  • It creates a new relationship between tables.

That’s correct! The function is specifically used to handle the inactive relationship between two tables in a data model.

3. True or False: Role-playing dimensions in Power BI are multiple instances of the same dimension used within a single fact table.

  • True (CORRECT)
  • False

That’s correct. Role-playing dimensions are the dimension table that can be used multiple times based on the role. 

4. True or False: The CROSSFILTER function in DAX allows users to establish custom relationships between tables.

  • True
  • False (CORRECT)

That’s correct! The USERELATIONSHIP function is used to establish custom relationships between tables.

5. An e-commerce company has a data model with an inactive relationship between the Product table and the Sales table. The active relationship is based on the Products sold column of the Product table, while the inactive relationship is based on the Products purchased column. You want to analyze sales data based on both Products sold and Products purchased. What are the possible solutions to analyze the data? Select all that apply.

  • You can delete the inactive relationship and use the Product dimension table for both analyses.
  • You can use the USERELATIONSHIP function. (CORRECT)
  • You can use the CROSSFILTER function to get both analyses.
  • You create a separate calculated table based on the Product table and rename it as Products Purchased. (CORRECT)

That’s correct! The USERELATIONSHIP function allows you to override the default relationship and tailor your analysis without the need for creating a separate table.

That’s correct. By creating a separate dimension table, you can resolve the issue and ensure more flexibility in your analysis.

SELF-REVIEW: USING TIME INTELLIGENCE TO COMPARE TO PREVIOUS YEAR

1. In Power BI, time intelligence DAX functions require which of the following to work properly? Select all that apply.

  • Presence of calculated columns in the data model.
  • A date dimension table with a column of dates. (CORRECT)
  • Properly defined relationships between the tables. (CORRECT)
  • Data imported from multiple sources.

That’s correct! A proper date dimension table with a date column is also a requirement for DAX time intelligence functions.

That’s correct! Appropriate relationships are essential for the proper and accurate functioning of DAX.

2. What is the value of change in the sales measure (YoY%) for August 2018?

  • 23.42%
  • -23.42% (CORRECT)
  • -2.9%

That’s correct. From the matrix, this is the correct value for August 2018.

3. True or False: To calculate the Sales previous year measure in DAX, you used the Revenue measure for your calculation. Is it possible to create the Sales previous year measure without using the Revenue measure?

  • True (CORRECT)
  • False

That’s correct! You can create the measure by referencing the Sales table’s Unit price and Quantity columns.

4. What is the advantage of time intelligence in Power BI for businesses?

  • It enables businesses to create hierarchies based on date data for visualization.
  • It enables businesses to analyze data through real-time data processing.
  • It enables businesses to analyze data based on time-related dimensions. (CORRECT)

Correct! Time intelligence helps businesses generate information about data over date/time dimensions.

5. Which DAX function is used to calculate running total over time?

  • TOTALYTD (CORRECT)
  • CALCULATE
  • DATESBETWEEN

That’s correct! The TOTALYTD function generates the year-to-date values for the specified measure.

6. True or False: The SAMEPERIODLASTYEAR function requires a separate Date dimension table.

  • True
  • False (CORRECT)

That’s correct. Time intelligence functions can be used with Power BI’s automatically generated Date dimension table.

KNOWLEDGE CHECK: TIME INTELLIGENCE CALCULATIONS IN DAX

1. Why is it important to have a properly defined date column in the data model to execute time intelligence functions?

  • To enable the use of advanced data visualization techniques.
  • To create calculated columns and measures in the data model.
  • To ensure data accuracy and consistency in time-based calculations. (CORRECT)

That’s correct! A properly defined and formatted date column in the data model is necessary for accurate time intelligence functions.

2. Which of the following features of Power BI can you use to add a date or common-dimension table to the data model? Select all that apply.

  • Manual data entry
  • Data Analysis Expressions (CORRECT)
  • The Data modeling tab
  • Power Query and M language (CORRECT)

That’s correct! You can create a new date dimension using DAX in the data model of Power BI.

That’s correct! M language and Power Query offer another visually better option for creating a date table in a Power BI data model.

3. True or False: DAX time intelligence functions can be used with Power BI’s autogenerated date/time dimension.

  • True (CORRECT)
  • False

That’s correct! Power BI’s autogenerated date dimension is based on the date column of the fact table and can be used for time intelligence functions.

4. You are tasked with evaluating a company’s year-to-date sales. You need to analyze YTD (year-to-date) values by integrating additional filter context to the calculations like the Product category, Region, and Reseller datasets. Which of the following DAX functions can you use to complete this task?

  • DATEADD
  • DATESYTD (CORRECT)
  • TOTALYTD

That’s correct! You can use the DATESYTD function with the CALCULATE function to incorporate additional filter context for the calculation.

5. A business recently launched a marketing campaign for its product line and wants to determine its impact on sales within a specific period. What time intelligence function can you employ to evaluate the campaign’s impact on sales? Select all that apply.

  • DATEADD (CORRECT)
  • DATESINPERIOD (CORRECT)
  • DATESBETWEEN (CORRECT)

The DATESADD function shifts the date range by a specified number of intervals (like days, months, or years). This is useful when you need to calculate metrics like sales over a shifted period or compare trends based on time intervals starting from a specific date.

The DATESINPERIOD function works similarly but allows you to define a specific period from a given start date and a duration (e.g., last 30 days, past quarter). It’s handy for evaluating time-based calculations within fixed periods in the past.

The DATESBETWEEN function takes both a start and an end date, letting you define a custom date range. It helps you compute values like sales over a specific time window, giving more flexibility when you need to focus on particular date ranges.

6. True or False: The CALENDARAUTO function takes both the start and the end date from a dataset.

  • True (CORRECT)
  • False

Correct! The CALENDARAUTO function takes both the start and the end date of a dataset.

7. True or False: A data model must include a Date table to facilitate time intelligence functions.

  • True
  • False (CORRECT)

That’s absolutely right! In Power BI, time intelligence calculations in DAX (Data Analysis Expressions) rely on a Date table in your data model. If a Date table is available, Power BI can generate useful time-based calculations, such as year-to-date (YTD), month-to-date (MTD), and previous period comparisons. These calculations are made easier when the Date table includes all the relevant date attributes, such as the year, quarter, month, day, and even fiscal periods if applicable.

MODULE QUIZ: USING DAX IN POWER BI

1. A hospital’s emergency department wants to calculate the number of visitors it has received this year compared to the previous year. How can it compare the values in Power BI using DAX? 

  • Using CALCULATE with the DATESBETWEEN function.
  • Using CALCULATE with the PREVIOUSYEAR function. (CORRECT)
  • Using SUM with the PREVIOUSYEAR function.

That’s correct! The PREVIOUSYEAR function, in combination with the CALCULATE function, can be used to compare this year’s visits to those of the previous year.

2. Adventure Works must generate data on sales of its mountain bikes in the United Kingdom for 2020. It must alter the evaluation context of its DAX measure to calculate the accurate values. Which of the following functions changes the context and computes an accurate sales amount? Select all that apply.

  • CALCULATE (CORRECT)
  • FILTER (CORRECT)
  • FILTERS

That’s right! Mountain bikes fall under the Product subcategory, and the United Kingdom represents a specific region. In both cases, filters utilizing CALCULATE functions are needed.

Exactly! The FILTER function can be used within CALCULATE to define the specific subcategory and region.

3. What is the outcome of the COUNTROWS function when deploy on a table with no filters applied?

  • It returns the total number of rows in a table. (CORRECT)
  • It returns the total number of unique rows in a table.
  • It returns the total number of distinct values in a column.

That’s correct! COUNTROWS returns the total number of rows in a table. 

4. True or False: The CROSSFILTER function can only establish a one-to-one relationship between tables.

  • True 
  • False  (CORRECT)

That’s correct! You can use the CROSSFILTER function to alter your filter conditions to achieve your desired results. It can also establish any filter direction based on the data model.

5. How can you use an inactive relationship to compute a single measure?

  • By selecting Delete on the active relationship.
  • By using the USERELATIONSHIP function. (CORRECT)
  • By changing the active relationship in the Modeling tab of Power BI.

That’s correct! USERELATIONSHIP can be used to establish a relationship for the current measure temporarily.

6. True or False: In Power BI, measures are calculated at the query level, while calculated columns are pre-calculated and stored in the data model. 

  • True (CORRECT)
  • False

That’s correct! Measures are calculations performed at the query level, so they’re dynamic. Calculated columns are stored in the data model and take up disc space.

7. Adventure Works has multiple stores located in different regions. Its dataset contains sales data for the past year stored in columns that include storeID, date, productID, quantity, and revenue. Which DAX functions can you use to find the top 5 products with the highest revenue for the current month in each store? Select all that apply.

  • FILTER (CORRECT)
  • RANKX
  • TOPN (CORRECT)

Exactly! The FILTER function refines the results by applying filters to any table column.

That’s right! The TOPN function is used to identify the top products based on a specified number within a dataset.

8. In the context of data model calculations, which statement best describes time intelligence?

  • Modified calculations in a filter context involving a date table. (CORRECT)
  • Modified calculations involving time-related dimensions.
  • Modified calculations involving COUNT, SUM and AVERAGE functions.

That’s correct! The DAX time intelligence function modifies the date filter context.

9. Which steps are required for creating a Date table using DAX in a data model? Select all that apply.

  • Column context
  • Row context (CORRECT)
  • Filter context (CORRECT)

Exactly! The row context is modified within the CALCULATE function for the specific measure.

That’s right! The CALCULATE function changes the filter context based on the filters defined in the formula.

10. When using the CALCULATE function, which of the following contexts can influence the overall evaluation context of the measure? Select all that apply.

  • Non-repudiation (CORRECT)
  • Availability
  • Confidentiality
  • Integrity

It guarantees that the information is not deniable and verifies that the sender of the message is indeed as he claims to be. Repudiating non-repudiation with guarantees cannot be, and upon it has complete authenticity to go full-fledged under such a system.

11. A marketing company wants to compare its daily website traffic. Which functions can it use to compare today’s web traffic with the day before?

  • SUM with PREVIOUSDAY.
  • CALCULATE with DATEADD.
  • CALCULATE with PREVIOUSDAY. (CORRECT)

That’s correct. The PREVIOUSDAY function compares values for the day before, and the CALCULATE function accepts the filter to compute the final values.

12. What is the purpose of the CROSSFILTER function when creating measures in DAX?

  • To remove filters from the table.
  • To establish temporary relationships between two tables.
  • To filter data between two tables based on the specific filter conditions. (CORRECT)

That’s correct! The CROSSFILTER function filters data based on specific filter conditions. These conditions let you control how filters flow without changing the default filter direction in the data model.

13. Adventure Works has multiple stores located in different regions. Its dataset contains sales data for the past year stored in columns that include storeID, date, productID, quantity, and revenue. Which DAX function can you use to calculate the running total of revenue for each store over time? Select all that apply.

  • SUMX (CORRECT)
  • TOTALYTD (CORRECT)
  • RANKX

Exactly! SUMX iterates over the table to perform the necessary calculations.

That’s right! The TOTALYTD function computes the running total of revenue for each store over time.

14. What is the purpose of time intelligence in Power BI?

  • To visualize time-related dates using custom visuals.
  • To manipulate date and time values in a data model.
  • To perform calculations involving periods and trends. (CORRECT)

That’s correct! Time intelligence functions aggregate and compare data over specific periods to identify trends and patterns.

15. Which of the following actions can you take to create time intelligence measures? Select all that apply.

  • Import a Date table from an Excel sheet.
  • Use Power BI’s automatically generated date/time dimension for time intelligence functions. (CORRECT)
  • Create a Date table using M and Power Query. (CORRECT)
  • Create a Date table using DAX. (CORRECT)

Exactly! Power BI’s autogenerated date/time dimension can also be used for time intelligence calculations.

That’s right! You can create a new table in Power Query using Power BI’s M language.

Exactly! You can also use DAX to build a new common date table within your data model.

16. True or False: The evaluation context in DAX does not change when executing DAX expressions.

  • True
  • False  (CORRECT)

That’s correct! The evaluation context always changes when calculating a DAX expression depending on the filters applied.

17. You have a table containing information about customer orders and another table with customer details. Considering the relationship between the tables, how can you count the number of orders each customer places? 

  • By using the COUNTROWS function with the RELATED function. (CORRECT)
  • By using the COUNTX function in combination with RELATED function.
  • By using the COUNTROWS function with FILTER function.

That’s correct! The COUNTROWS function returns the total number of rows from the order column. The RELATED function references the related table in the data model.

18. The syntax of CROSSFILTER function is:

CROSSFILTER(____________________________)

  • Table1, Table2[Column2], Direction
  • Table1[Column1], Table2[Column2], Direction (CORRECT)
  • Table1[Column1], [Column2], Direction

That’s correct! Using the CROSSFILTER function requires both column names with associated tables and the direction of the filter.

19. Why is the role-playing dimension useful in Power BI?

  • It enables the creation of custom visuals in reports.
  • It simplifies data modeling by eliminating the need for relationships.
  • It allows for different views of the same dimension in a report. (CORRECT)

That’s correct! A role-playing dimension can be used to view data from different perspectives by playing multiple roles within a data model.

20. Which of the following statements describes the similarity of measures and calculated columns in an import model?

  • Both can be used to summarize data. (CORRECT)
  • Both are evaluated during the data refresh.
  • Both can be used to store values in a data model.

That’s correct! Both measures and calculated columns can summarize data by various attributes used to define them.

21. Which syntax can you use to compute sales data for the previous month?

  • Sales = CALCULATE 
  • ( 
  •     SUM ( Sales[Total Sales] ), 
  •     PREVIOUSMONTH ( Date[DateKey] ) 
  • ) (CORRECT)
  • Sales = CALCULATE 
  • ( 
  •     Sales[Total Sales] ), 
  •     PREVIOUSMONTH ( Date[DateKey] ) 
  • ) 
  • Sales = SUM 
  • ( 
  •     Sales[Total Sales] ), 
  •     PREVIOUSMONTH ( Date[DateKey] ) 
  • )

That’s correct! CALCULATE and SUM compute the sales amount, and PREVIOUSMONTH compares the dates to the previous month.

22. Which of the following functions in DAX can you use to alter the evaluation context of a calculation? Select all that apply.

  • FILTER
  • USERELATIONSHIP
  • CALCULATE (CORRECT)

That’s correct! CALCULATE, and its companion function CALCULATETABLE are the only functions of DAX that can change the context of calculations.

23. Your data model contains a role-playing dimension based on a Date table that analyzes each transaction’s order and delivery dates. What solutions can you use to distinguish between the analyses based on the order and delivery dates? Select all that apply.

  • Create separate measures for the order date and delivery dates by using the USERELATIONSHIP function. (CORRECT)
  • Use the same date hierarchy for the order and delivery dates.
  • Clone the Date table and use it to create measures based on the Delivery date. (CORRECT)
  • Rename the role-playing dimension as Delivery date.

Exactly! The USERELATIONSHIP function in DAX allows you to override the existing relationship between tables. You can use it to relate the Delivery date column to the Date column in the Date table for measure calculation.

That’s right! Cloning the Date table, renaming it as Delivery date, and establishing a relationship also resolves the issue.

24. Adventure Works has multiple stores located in different regions. Its dataset contains sales data for the past year stored in columns that include storeID, date, productID, quantity, and revenue. Which DAX functions can the company use to identify the date with the highest revenue for each store? Select all that apply.

  • MAX (CORRECT)
  • RANKX
  • TOPN
  • FILTER (CORRECT)

That’s correct! The MAX function returns the maximum value of revenue for each store.

That’s correct! The FILTER function filters the date column for the highest revenue.

25. A car company is evaluating sales of its latest model. It changes the default evaluation context of its calculation to compute the total sales for a specific color. Which of the following functions can the company deploy to compute this value?

  • CROSSFILTER
  • FILTER
  • CALCULATE (CORRECT)

That’s correct! The company can incorporate color and other filters into its calculation using the CALCULATE function.

26. A Sales table lists each transaction as a separate row. Which of the following DAX functions can summarize the total number of transactions in the table?

  • DISTINCTCOUNT
  • COUNTROWS (CORRECT)
  • SUM

That’s correct! The COUNTROWS function can summarize the transactions in a table by returning the total number of rows.

27. An e-commerce company wants to analyze its sales, profit, and profit margin data. Which of the following scenarios should it use measures instead of calculated columns?

  • To calculate a percentage based on values from multiple columns in a table. (CORRECT)
  • To perform row-level calculations on a single column of a table.
  • To add a new column to a table that performs aggregations on a numeric column.

That’s correct! Measures can be created by referencing multiple columns from tables using multiple DAX functions.

CONCLUSION – Using Data Analysis Expressions (DAX) in Power BI

In conclusion, this module provides learners with a thorough understanding of the Data Analysis Expressions (DAX) language, focusing on its syntax, practical applications, and integration with database tables. By examining various business use cases, learners discover how DAX can address real-world challenges. The inclusion of time intelligence enhances their ability to perform advanced time-based calculations and analyses. Ultimately, this module equips learners to leverage DAX effectively in diverse business contexts, enabling them to extract valuable insights and make informed, data-driven decisions.

Leave a Comment