259 Teil4: DAX & Time Intelligence

1. HOTSPOT You have a Power BI model that contains a table named Sales and a related date table. Sales contains a measure named Total Sales. You need to create a measure that calculates the total sales from the equivalent month of the previous year. How should you complete the calculation? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area

HOTSPOT You are enhancing a Power BI model that has DAX calculations. You need to create a measure that returns the year-to-date total sales from the same date of the previous calendar year. Which DAX functions should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area

1. HOTSPOT You are building a financial report by using Power BI. You have a table named financials that contains a column named Date and a column named Sales. You need to create a measure that calculates the relative change in sales as compared to the previous quarter. How should you complete the measure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area

1. DRAG DROP You are creating a Power BI model and report. You have a single table in a data model named Product. Product contains the following fields:

✑ ID

✑ Name

✑ Color

✑ Category CertyIQ

✑ Total Sales

You need to create a calculated table that shows only the top eight products based on the highest value in Total Sales. How should you complete the DAX expression? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. Select and Place:

1. DRAG DROP You need create a date table in Power BI that must contain 10 full calendar years, including the current year. How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point

1. HOTSPOT You have a Power BI report that contains a measure named Total Sales. You need to create a new measure that will return the sum of Total Sales for a year up to a selected date. How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area

1. DRAG DROP You are modifying a Power BI model by using Power BI Desktop. You have a table named Sales that contains the following fields

You have a table named Transaction Size that contains the following data

You need to create a calculated column to classify each transaction as small, medium, or large based on the value in Sales Amount. How should you complete the code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Select and Place

1. HOTSPOT You are creating a quick measure as shown in the following exhibit

You need to create a monthly rolling average measure for Sales over time.How should you configure the quick measure calculation? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point

1. You have the Power BI data model shown in the following exhibit

The Sales table contains records of sales by day from the last five years up until today’s date. You plan to create a measure to return the total sales of March 2021 when March 2022 is selected. Which DAX expression should you use?

1. DRAG DROP You have a Power BI data model that contains two tables named Products and Sales. A one-to-many relationship exists between the tables. You have a report that contains a report-level filter for Products. CertyIQ You need to create a measure that will return the percent of total sales for each product. The measure must respect the report-level filter when calculating the total. How should you complete the DAX measure? To answer, drag the appropriate DAX functions to the correct targets. Each function may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point

1. HOTSPOT CertyIQ You have a Power BI data model that contains a table named Stores. The table has the following columns: • Store Name • Open Date • Status • State • City You need to create a calculated column named Active Store Name that meets the following requirements: • When the value of the Status column is “A”, the value in the Store Name column must be returned. • When the value of the Status column is NOT “A”, the value in the Store Name column that is prefixed with "Inactive - " must be returned. How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

92.You have a Power BI model that contains a table named Employee. The table contains the following data

Each employee has one manager as shown in the ParentEmployeeID column. All reporting paths lead to the CEO at the top of the organizational hierarchy. You need to create a calculated column that returns the count of levels from each employee to the CEO. Which DAX expression should you use?

1. HOTSPOT You have the Power BI data model shown in the following exhibit

You need to create a measure to count the number of product categories that had products sold during a selected period. How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

102.You have a Power BI model that contains a table named Sales. The Sales table contains the following columns:

•Order Line ID

•Product ID

•Unit Price

•Order ID

•Quantity

Orders are uniquely identified by using the order ID and can have multiple order lines. Each order line within an order contains a different product ID. You need to write a DAX measure that counts the number of orders. Which formula should you use?

1. HOTSPOT You are creating a Power BI model in Power BI Desktop. You need to create a calculated table named Numbers that will contain all the integers from -100 to 100. CertyIQ How should you complete the DAX calculation? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

1. You have a Power BI model that contains a table named Date. The Date table contains the following columns:

•Date

•Fiscal Year

•Fiscal Quarter

•Month Name

•Calendar Year

•Week Number

•Month Number

•Calendar Quarter

You need to create a calculated table based on the Date table. The calculated table must contain only unique combinations of values for Calendar Year, Calendar Quarter, and Calendar Month. Which DAX function should you include in the table definition?

1. HOTSPOT CertyIQ You have a Power BI model that contains the following data

The Date table relates to the Sales table by using the Date columns. You need to create a calculated table that will contain the following:

•A row for each year

•A column that contains the total sales per year

How should you complete the DAX calculation? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point

1. You are creating a Power BI model to analyze inventory. You load data into three tables named Date, Product, and Inventory. The Inventory table relates to the Date and Product tables by using one-to-many relationships. Inventory data is recorded daily with no exceptions. The correct inventory quantity for a given product in a month is the last recorded value for that month. You need to write a DAX measure that will show the correct inventory value when a user analyzes inventory by year, month, or date. How should you complete the measure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point

1. HOTSPOT CertyIQ You are creating a Power BI report that will show the number of current employees over time. The report will use Import storage mode for all tables. The employment data will be imported from Azure SQL Database in a monthly snapshot. The data will be stored in a table named Headcount and will contain the following:

•One row per employee for each month the employee is employed

•In each row, a date key that shows the first day of the month of each snapshot

You have a related date table that contains dates for the years 2020 to 2030. You need to create a semi-additive DAX measure that will return the count of employees for the last available date in a year, quarter, or month. How should you complete the measure? To answer, select the appropriate options in the answer area.

1. You have a Power BI model that contains the following data

The Date table relates to the Sales table by using the Date columns. The model contains the following DAX measure. Total Sales = SUM(Sales[Sale]) You need to create another measure named Previous Quarter to display the sales one quarter before the selected period.

Which DAX calculation should you use?

1. You are reviewing a Power BI data model. You have a calculated table that has the following definition. ProductList = INTERSECT ( ProductsGroupA, ProductsGroupB ) You need to identify the results of the DAX expression. Which rows will be returned in ProductList?

1. You have a Power BI model that contains a table named Employees. The table contains the following columns:

•Employee ID

•First Name

•Last Name

•Department

•Salary

Each employee is uniquely identified by using Employee ID. You need to create a DAX measure that will calculate the average salary of all the employees in the sales department. Which DAX expression should you use?

1. You have a Power BI model that contains two tables named Population and Date. The Population table contains two columns named PopulationAmount and DateKey. DateKey contains date values that represent the first day of a year and are used to create a many-to-one relationship with the Date table. The Power BI model contains two measures that have the following definitions. Total Population = Sum(‘Population’[PopulationAmount]) 2023 Population = CALCULATE([Total Population], ‘Date'[Year] = 2023) You create a table visual that displays Date[Year] and [2023 Population]. What will the table visual show?

1. DRAG DROP You have a Power BI table named Customer that contains a field named Email Address. You discover that multiple records contain the same email address. You need to create a calculated column to identify which records have duplicate email addresses. How should you complete the DAX expression for the calculated column? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point. Select and Place