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?
A. DISTINCTCOUNT(‘Employees’[Salary])
B. CALCULATE(DISTINCTCOUNT(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
C. CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
D. AVERAGE(‘Employees’[Salary])
Show Answer
Correct Answer: C
Explanation: The required measure must compute the average of the Salary column while filtering the Employees table to only rows where Department = "Sales". CALCULATE modifies the filter context and AVERAGE returns the mean salary. DISTINCTCOUNT counts unique salary values rather than averaging them, and AVERAGE alone does not restrict the calculation to the Sales department.
Question 132
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?
A. all the rows in ProductsGroupB that have a matching row in ProductsGroupA
B. all the rows in both tables
C. all the rows in ProductsGroupA that have a matching row in ProductsGroupB
D. all the rows in ProductsGroupA that have no matching row in ProductsGroupB.
Show Answer
Correct Answer: C
Explanation: INTERSECT(table1, table2) returns the rows that exist in both tables, but it preserves the rows (including duplicates) and lineage from the first table expression. Therefore, INTERSECT(ProductsGroupA, ProductsGroupB) returns the rows from ProductsGroupA that have matching rows in ProductsGroupB.
Question 135
HOTSPOT
-
You have a query named All Sales that imports sales data into a Power BI model.
You plan to create a star schema by separating columns into separate queries and performing further transformations. The solution must meet the following requirements:
• Use All Sales as the source for three other queries named Sales Fact, Product Dimension, and Customer Dimension.
• Minimize maintenance effort.
What should you do to create the Sales Fact query, and for which query should you clear Enable load? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Show Answer
Correct Answer: Reference the All Sales query.
All Sales
Explanation: Use Reference so the fact and dimension queries inherit changes from the staging query, minimizing maintenance. Disable Enable load on the staging query (All Sales) because it is only an intermediate source and should not be loaded into the model.
Question 136
HOTSPOT
-
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.
Explanation: Use COUNTROWS because after filtering to the last date in the current period, there is one row per current employee. LASTDATE over the Date dimension returns the last date in the current filter context (year/quarter/month), providing the required semi-additive behavior.
Question 137
You use Power Query to import two tables named Order Header and Order Details from an Azure SQL database. The Order Header table relates to the Order Details table by using a column named Order ID in each table.
You need to combine the tables into a single query that contains the unique columns of each table.
What should you select in Power Query Editor?
A. Merge queries
B. Combine files
C. Append queries
Show Answer
Correct Answer: A
Explanation: Use Merge queries to join the Order Header and Order Details tables on the common Order ID column, producing a single query that includes the unique columns from both tables. Append queries adds rows from similarly structured tables, while Combine files is for combining multiple files rather than joining database tables.
Question 138
You publish a dataset to the Power BI service. The dataset contains a connection to an on-premises Microsoft SQL Server database.
You attempt to configure a scheduled refresh but cannot select the appropriate on-premises data gateway.
You confirm the following with the administrator of the gateway:
• You have the appropriate permissions to use the gateway.
• The data source was created on the gateway.
• The gateway has a status of Running.
What is the most likely reason the gateway is unavailable?
A. The type of data source is not supported by the on-premises data gateway.
B. The server name in the PBIX file does not match the data source name in the gateway.
C. The credentials for the data source are invalid.
D. The data source is configured to use single sign-on (SSO).
Show Answer
Correct Answer: B
Explanation: Power BI matches datasets to gateway data sources using the connection details, including the server and database names. If the server name (or other connection details) in the PBIX file does not exactly match the data source configured on the gateway, the gateway will not be available for selection even if it is running, the data source exists, and the user has permission. SQL Server is supported by the on-premises data gateway, invalid credentials do not typically prevent the gateway from appearing for selection, and SSO configuration is not the most likely cause in this scenario.
Question 139
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.
You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.
What should you do?
A. Apply the Parse function from the Data transformations options to the Logged column.
B. Change the data type of the Logged column to Date.
C. Split the Logged column by using at as the delimiter.
D. Create a column by example that starts with 2018-12-31.
Show Answer
Correct Answer: C
Explanation: The value is text in the form '2018-12-31 at 08:59', which is not directly recognized as a date because of the literal 'at'. Splitting the column on 'at' produces a date column ('2018-12-31') and a time column ('08:59'). The date column can be recognized/typed as a Date, enabling Power BI's built-in date hierarchy. Simply changing the type to Date will fail on the original text, and creating a column by example typically yields text and requires additional transformation.
Question 140
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?
A. one row per year that contains blank values for every year except 2023
B. one row per date that contains the population value for the corresponding year repeated in each row
C. a single row for the year 2023 that contains the related population value
D. one row per year that contains the same value repeated for each year
Show Answer
Correct Answer: D
Explanation: CALCULATE modifies the filter context by applying the filter 'Date'[Year] = 2023. Because this filter is on the same Date table as the row context from the visual, it replaces the existing Year filter rather than intersecting with it. Thus, for every row (regardless of the displayed year), the measure evaluates using Year = 2023 and returns the 2023 population repeatedly. The visual still lists all years because Date[Year] is the grouping column; only the measure is overridden.
Question 142
You have a Power BI workspace named BI Data that contains a dataset named BI Finance.
You have the Build permission for the BI Finance dataset, but you do NOT have permissions for the workspace.
You need to connect to BI Finance and create a report.
Which two actions should you perform? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. From the Power BI service, create a dataflow to the dataset by using DirectQuery.
B. From Power BI Desktop, connect to a Dataverse data source.
C. From the Power BI service, create a new report and select a published dataset.
D. From Power BI Desktop, connect to a shared dataset.
Show Answer
Correct Answer: C, D
Explanation: Build permission on a Power BI dataset allows creating reports from that shared (published) dataset both in the Power BI service and from Power BI Desktop using a live connection to a shared dataset. It does not grant workspace permissions, and creating a dataflow or connecting to Dataverse is unrelated to using the existing dataset.
Question 143
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys:
• Due Date
• Order Date
• Delivery Date
You need to support the analysis of sales over time based on all three dates at the same time.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables. You create active relationships between the sales table and each date table.
Does this meet the goal?
A. Yes
B. No
Show Answer
Correct Answer: A
Explanation: Yes. Creating separate copies of the Date dimension (role-playing dimensions) such as Due Date, Order Date, and Delivery Date allows each copy to have its own active relationship to the Sales table using the corresponding foreign key. Renaming the original table and creating calculated table copies is a valid way to achieve simultaneous analysis by all three dates. The limitation is only that there cannot be multiple active relationships between the same two tables; separate date tables avoid that ambiguity.
$19
Get all 371 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.
