Step2Study

Microsoft Certified: Power BI Data Analyst Associate practice questions

204 free questions with answers and explanations.

Practice test
  1. 51.A data modeler is building a Power BI model for a global company with a complex organizational structure. The 'Employees' table contains 'EmployeeID', 'EmployeeName', and 'ManagerID'. The 'ManagerID' refers to another 'EmployeeID' within the same table. The modeler needs to create a calculation that identifies the top-level CEO in the hierarchy and then count all direct and indirect reports under a specific manager. Which DAX functions are most suitable for navigating this parent-child hierarchy?Model the data
  2. 52.A data modeler is building a Power BI model for a financial institution. The model includes a 'Transactions' table with 'TransactionID', 'AccountID', 'TransactionDate', and 'Amount'. The modeler needs to calculate the cumulative sum of 'Amount' over time for each 'AccountID'. Which DAX pattern is appropriate for creating a running total that respects account-level context?Model the data
  3. 53.A data modeler is optimizing a Power BI report with several complex measures and calculated columns. The report is experiencing slow refresh times and sluggish interaction. Upon reviewing the model, the modeler notices that many calculated columns perform aggregations across large tables. Which of the following actions would most effectively improve the model's performance?Model the data
  4. 54.A data analyst is connecting to a web service that returns data in a JSON array format. Each object in the array represents a customer and has nested fields for 'ContactInfo' (containing 'Email' and 'Phone') and 'Address' (containing 'Street', 'City', 'State', 'Zip'). The analyst needs to flatten this structure so that 'Email', 'Phone', 'Street', 'City', 'State', and 'Zip' appear as individual columns in the Power BI data model. Which Power Query transformation should be used to achieve this?Prepare the data
  5. 55.A data analyst is importing a dataset into Power BI from a SQL Server database. The database contains sensitive customer information, and for a specific report, only a subset of the columns from a very large table is needed. To optimize performance and reduce data transferred, the analyst wants to ensure that Power Query pushes down the column selection operation to the source database. Which Power Query transformation is crucial for achieving this 'query folding' for column selection?Prepare the data
  6. 56.A data analyst is developing a Power BI model for a manufacturing company. The model contains a 'Production' fact table and a 'Machines' dimension table. The 'Machines' table has a 'MaintenanceDate' column that signifies the last maintenance performed. The analyst needs to calculate the total production for machines that have not had maintenance in the last 90 days, based on the current date selected in the report. Which DAX pattern should be used to identify these machines?Model the data
  7. 57.A data analyst is working on a Power BI model for a manufacturing company. The model includes a 'Production' table with 'ProductID', 'ProductionDate', and 'UnitsProduced'. The analyst needs to create a measure that calculates the moving average of units produced over the last 7 days. Which DAX pattern should the analyst use?Model the data
  8. 58.A data analyst is preparing sales data for a Power BI report. The data contains a 'SalesDate' column in text format (e.g., '2023-01-15 14:30:00'). The analyst needs to extract only the year from this column to analyze annual sales trends. Which Power Query transformation step should be applied to achieve this efficiently?Prepare the data
  9. 59.A data analyst is importing a dataset into Power BI from a CSV file. The file contains a column named 'Product_ID' which is intended to be a unique identifier, but upon initial inspection in Power Query Editor, it appears to contain leading and trailing spaces in some entries. Which Power Query transformation should be applied FIRST to ensure data consistency for this column?Prepare the data
  10. 60.A data modeler is building a Power BI report for a subscription-based service. The model has a 'Subscriptions' fact table with 'SubscriptionID', 'StartDate', and 'EndDate'. The modeler needs to calculate the number of active subscriptions at the end of each month. Subscriptions are considered active if their 'StartDate' is on or before the month-end date and their 'EndDate' is after the month-end date. Which DAX pattern effectively calculates this 'snapshot' measure?Model the data
  11. 61.A data architect is designing a Power BI report for a global manufacturing company. The report needs to display real-time production line status from an IoT hub, which streams data continuously. Historical production data (last 5 years) is stored in an Azure Data Lake Gen2 and is used for trend analysis. The report must offer both immediate status updates and historical insights. Which combination of data connectivity modes would be most appropriate for these two data sources?Prepare the data
  12. 62.A data analyst is working with a sales dataset in Power Query. The 'SalesDate' column is currently stored as text and contains values in various formats such as '2023-01-15', '1/2/2023', and 'Jan 5, 2023'. The analyst needs to convert this column to a proper 'Date' data type to enable date-based filtering and calculations. Which Power Query operation is the most robust way to handle these mixed date formats during conversion?Prepare the data
  13. 63.A data analyst is importing data from a web API that returns customer order details. The API response is in JSON format, and each order record contains a nested object called 'ShippingAddress' which itself contains fields like 'Street', 'City', and 'ZipCode'. To use these address components directly in the Power BI report, what Power Query transformation is required after initially connecting to the JSON source?Prepare the data
  14. 64.A data modeler is creating a Power BI model for financial analysis. The model contains a 'Transactions' fact table with 'TransactionDate' and 'Amount'. The modeler needs to calculate a cumulative sum (running total) of 'Amount' over time, specifically for a given year, resetting at the start of each new year. Which DAX pattern effectively calculates this Year-to-Date (YTD) cumulative sum?Model the data
  15. 65.A data modeler is designing a Power BI model for a product catalog. The 'Products' table has columns 'ProductID', 'ProductName', and 'ProductCategory'. The modeler needs to create a hierarchy that allows users to drill down from 'ProductCategory' to 'ProductName'. Where should this hierarchy be defined?Model the data
  16. 66.A data analyst is importing data from a web API that returns product information in JSON format. The API response contains a list of products, where each product is an object with properties like 'ProductID', 'ProductName', and a nested 'Category' object (e.g., `"Category": {"CategoryID": 1, "CategoryName": "Electronics"}`). The analyst needs to access 'CategoryName' and display it as a top-level column in the product table. Which Power Query operation is used to achieve this?Prepare the data
  17. 67.A data analyst is developing a Power BI model for an e-commerce platform. The model includes a 'Sales' table with 'OrderID', 'ProductID', 'Quantity', and 'UnitPrice' columns. The analyst needs to calculate the 'Total Revenue' for each order item, which is 'Quantity' multiplied by 'UnitPrice'. Which DAX function is suitable for creating a measure that correctly calculates this row-level multiplication and then sums the results?Model the data
  18. 68.A data modeler is designing a Power BI model for a retail company to analyze sales performance. The model includes a 'Sales' fact table and a 'Products' dimension table. The 'Products' table has a column called 'ProductCategory' and another called 'ProductSubCategory'. The modeler wants to create a hierarchy that allows users to drill down from category to subcategory. Which of the following is the most efficient way to implement this hierarchy in Power BI?Model the data
  19. 69.A data modeler is creating a Power BI report for a subscription service. The model includes a 'Subscriptions' table with 'SubscriptionID', 'StartDate', and 'EndDate'. The modeler needs to calculate the number of active subscriptions at the end of each month. Which DAX function or pattern is best suited for this scenario?Model the data
  20. 70.A Power BI model includes a 'Customers' table and an 'Orders' table. The 'Customers' table contains 'CustomerID' and 'CustomerSegment' columns. The 'Orders' table contains 'OrderID', 'CustomerID', and 'OrderValue' columns. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Orders[CustomerID]'. The data analyst needs to calculate the average order value for each customer segment. Which DAX function should be used to achieve this efficiently?Model the data
  21. 71.A data analyst is working on a Power BI model for sales forecasting. The model includes a 'Sales' table and a 'Date' table. The analyst needs to calculate the total sales for the same period as the current selection, but for the previous year. For example, if the current selection is Q1 2023, the measure should show sales for Q1 2022. Which DAX time intelligence function is most suitable for this requirement?Model the data
  22. 72.A client is developing a Power BI model to analyze product sales performance over time. The model includes a 'Sales' table, a 'Products' table, and a 'Date' table. The client wants to calculate the total sales for the 'current' month (based on the latest date in the 'Date' table that has sales data) and compare it to the total sales of the previous month. Which DAX time intelligence function is best suited to retrieve the sales for the previous month in this scenario?Model the data
  23. 73.A data modeler is designing a Power BI model for a manufacturing plant. The model needs to track the 'Total Production Quantity' for each product. The production data is stored in a 'Production' table with 'ProductID' and 'Quantity' columns. The 'Products' table has 'ProductID' and 'ProductName'. The modeler chooses to create a simple measure `Total Production Quantity = SUM(Production[Quantity])`. After deployment, a user creates a table visual that displays 'ProductName' and the new 'Total Production Quantity' measure. What is the filter context for the 'Total Production Quantity' measure when evaluated for a specific 'ProductName' in this visual?Model the data
  24. 74.A data analyst is integrating customer data from a legacy system. The system exports data as a series of text files, each containing customer information where fields are separated by a pipe character ('|'). The customer address field, 'StreetAddress', sometimes contains commas within the address itself, which could be misinterpreted if not handled correctly. Which Power Query transformation should be used to correctly separate the fields into individual columns?Prepare the data
  25. 75.A data modeler is preparing a Power BI report that combines sales data from an Azure SQL Database with product catalog information from an Excel file stored in SharePoint Online. The product catalog file is updated weekly. The sales data, however, is continuously updated, and the report must reflect the latest sales figures as quickly as possible. To optimize performance and ensure data freshness, how should these two data sources be configured in Power BI?Prepare the data
  26. 76.A data modeler is designing a Power BI model for a global company. The data includes sales transactions from various countries and currencies. To ensure accurate financial reporting, all transaction amounts must be converted to a common base currency (USD) using historical exchange rates. The exchange rates are stored in a 'ExchangeRates' table with 'Date', 'FromCurrency', 'ToCurrency', and 'Rate' columns. The 'Sales' table has 'SaleDate', 'Amount', and 'Currency' columns. Which data modeling technique is most appropriate to handle this currency conversion efficiently and accurately?Model the data
  27. 77.A data analyst is preparing a sales dataset in Power Query. The 'ProductID' column is intended to be a unique identifier, but profiling reveals leading and trailing spaces in some entries (e.g., ' P123 ' instead of 'P123'). Additionally, some entries are entirely whitespace or null. The analyst first needs to remove the leading/trailing spaces and then handle the problematic null/whitespace entries. What is the correct sequence of transformations?Prepare the data
  28. 78.A data modeler is creating a Power BI report that compares product sales across different regions. The model contains a 'Sales' table, a 'Products' table, and a 'Regions' table. The 'Sales' table has a 'RegionID' column which is related to 'Regions[RegionID]'. The modeler needs to calculate the total sales for a specific product category (e.g., 'Electronics') but wants to see this total sales value for 'Electronics' repeated across all regions in a matrix visual, regardless of the region filter applied to other measures in the visual. Which DAX pattern effectively achieves this?Model the data
  29. 79.A data analyst is building a Power BI report for a sales team. The sales data is stored in an Excel workbook, and the analyst needs to combine detailed sales transactions (Sheet1) with customer demographic information (Sheet2). Both sheets share a common 'CustomerID' column. The requirement is to include all sales transactions, and only the customer information that has a matching 'CustomerID' in the sales data. Which type of join in Power Query should the analyst use?Prepare the data
  30. 80.A data modeler is optimizing a large Power BI model with several disconnected tables used for 'what-if' analysis and scenario planning. These tables contain parameters that users can adjust. The model is experiencing slow query performance when interacting with visuals that incorporate these parameters. Which of the following modeling practices would most effectively improve the performance of such a model?Model the data
  31. 81.A data analyst is working with a large dataset in Power Query, and has applied several transformation steps. To optimize performance and reduce memory consumption in the Power BI model, the analyst wants to ensure that only the absolutely necessary columns are loaded and that any intermediate columns used only for transformation (e.g., helper columns for calculations that are no longer needed) are removed before loading to the model. Which Power Query action should be performed to achieve this?Prepare the data
  32. 82.A data analyst is preparing a dataset in Power Query for a Power BI report. The source data contains a column named `TransactionDate` which is currently of type `Text` and contains values in various formats, such as '2023-01-15', '1/2/2023', and 'January 3, 2023'. The analyst needs to convert this column to a `Date` data type. Which Power Query transformation option is most likely to handle these varied formats successfully without requiring multiple 'Replace Values' steps?Prepare the data
  33. 83.A data modeler is building a Power BI model for a global sales organization. The model contains a 'Sales' fact table and a 'Currencies' dimension table. The 'Sales' table has a 'SaleAmount' in local currency and a 'CurrencyKey' column. The 'Currencies' table has 'CurrencyKey', 'CurrencyCode', and 'ExchangeRate' columns. The modeler needs to display 'SaleAmount' converted to USD using the 'ExchangeRate' from the 'Currencies' table. Which DAX pattern is most suitable for this row-level currency conversion?Model the data
  34. 84.A data analyst is building a Power BI model to track employee performance. The model includes an 'Employees' table (EmployeeID, ManagerID, EmployeeName) and a 'Sales' table (EmployeeID, SalesAmount). The 'Employees' table has a self-referencing relationship where 'ManagerID' links to 'EmployeeID' to represent the organizational hierarchy. The analyst needs to create a measure that calculates the 'Total Sales' for an employee and all their direct and indirect subordinates. Which DAX function is essential for traversing this hierarchy to sum sales?Model the data
  35. 85.A data modeler has developed a Power BI model with a complex star schema, including a 'Sales' fact table and several dimension tables like 'Product', 'Customer', and 'Date'. The 'Sales' table has millions of rows. The model is experiencing slow query performance, especially when users filter by attributes from multiple dimension tables simultaneously. Which of the following is the most likely cause of this performance issue?Model the data
  36. 86.A data engineer is preparing a large dataset in Power Query that aggregates daily sales figures from a data warehouse. The dataset contains a 'SaleDate' column of type Date. To analyze sales trends by month and quarter, the engineer needs to add new columns for 'Month Number', 'Month Name', and 'Quarter Number'. Which Power Query feature should be used to efficiently create these new columns?Prepare the data
  37. 87.A data analyst is importing data into Power BI from a web API that returns JSON data. The JSON data contains a nested array of objects representing 'OrderItems' within each 'Order' record. The analyst needs to expand this 'OrderItems' array so that each item in the array becomes a separate row in the main table, while duplicating the parent 'Order' information for each expanded row. Which Power Query transformation concept is required for this scenario?Prepare the data
  38. 88.A data analyst is designing a Power BI model for a retail company. The 'Sales' table contains 'TransactionID', 'ProductID', 'Quantity', and 'Price' columns. The analyst needs to calculate the 'Total Sales Amount' as 'Quantity * Price' for each transaction. This calculation will be used frequently in various visuals and measures. Which is the most appropriate way to implement this calculation in the Power BI model?Model the data
  39. 89.A data modeler is designing a Power BI data model for an online retail company. The company wants to analyze customer lifetime value (CLTV). The 'Sales' table contains 'CustomerID', 'OrderDate', and 'OrderTotal'. The 'Customers' table contains 'CustomerID' and 'CustomerJoinDate'. You need to calculate the total sales for each customer up to their last purchase date. Which DAX function is most appropriate for iterating through sales rows for each customer to sum their order totals?Model the data
  40. 90.A data modeler is building a Power BI model for a manufacturing company. The model contains a 'Production' fact table with daily production quantities and a 'Products' dimension table. The 'Products' table has a 'ProductCategory' column. The modeler needs to calculate the 'Total Production Quantity' for products belonging to the 'Electronics' category. However, the 'Production' table does not have a direct 'ProductCategory' column, only 'ProductID'. Which DAX function is most appropriate to achieve this calculation while ensuring optimal performance?Model the data
  41. 91.A data analyst is working with a table in Power Query where a 'ProductCategory' column contains values like ' Electronics ', 'Clothing ', and ' Food '. Due to inconsistent data entry, some values have leading or trailing spaces. These spaces are causing issues with grouping and filtering. Which Power Query transformation should the analyst use to remove these unwanted spaces?Prepare the data
  42. 92.A data modeler is optimizing a Power BI model for a large dataset. The model has several relationships between tables. The modeler observes that a relationship between 'Orders'[CustomerID] and 'Customers'[CustomerID] is inactive, but is occasionally needed for specific calculations that diverge from the default active relationship. How should the modeler activate this inactive relationship for a specific measure without affecting other parts of the model?Model the data
  43. 93.A data modeler is optimizing a Power BI model for a large enterprise. The model contains a 'Sales' fact table and several dimension tables. The 'Sales' table has a composite key consisting of 'OrderDateKey', 'ProductKey', and 'CustomerKey'. There are also separate 'Date', 'Product', and 'Customer' dimension tables. To improve query performance and reduce memory usage, the modeler wants to ensure the relationships between the fact and dimension tables are optimally configured. What is the most effective approach for establishing these relationships?Model the data
  44. 94.A data analyst is working with a sales dataset in Power Query. The dataset includes a 'SalesDate' column, which is currently of a 'Date' data type. The analyst needs to create a new column that indicates the 'Year' of each sale for trend analysis. Which Power Query transformation should be used to extract the year from the 'SalesDate' column?Prepare the data
  45. 95.A data analyst is working in Power Query with a table containing customer data. The table includes a column named `CustomerName` and a column named `CustomerID`. The analyst needs to create a new column named `CustomerIdentifier` that combines these two columns into a single string, formatted as 'Name (ID)'. For example, if `CustomerName` is 'John Doe' and `CustomerID` is 'C123', the new column should show 'John Doe (C123)'. Which Power Query transformation should the analyst use?Prepare the data
  46. 96.A Power BI report requires sales data from a legacy system that exports monthly data into separate Excel files. Each Excel file contains multiple sheets, but only the sheet named 'Sales Summary' is relevant. The 'Sales Summary' sheet in each file has the sales data starting from row 5, with the actual headers in row 4. Additionally, the first three columns of the 'Sales Summary' sheet (e.g., 'Region', 'Date', 'Product') contain metadata that should be kept, but the remaining columns represent monthly sales figures (e.g., 'Jan-2023', 'Feb-2023') which need to be unpivoted to a single 'Month' column and a 'SalesAmount' column. What is the most efficient sequence of Power Query transformations to achieve this for all files in a folder?Prepare the data
  47. 97.A data analyst has published a Power BI report to a workspace. The analyst now needs to ensure that a specific group of users can only access a curated set of reports and dashboards, and that this content is easily discoverable for them. The analyst also wants to control who can share this curated content further. What is the most appropriate Power BI feature to use for this scenario?Deploy and maintain assets
  48. 98.A sales director wants a Power BI report to analyze sales performance by territory. The report needs to display both the 'Total Sales' and the 'Sales Growth Percentage' year-over-year for each territory. The director specifically requests a visual that can show both values for each territory side-by-side, allowing for quick comparison while clearly differentiating between the absolute sales value and the percentage change. Which visual type is most effective for this dual-metric comparison?Visualize and analyze the data
  49. 99.A sales manager reviews a Power BI report daily to track regional sales performance. The manager needs to quickly identify regions where sales have dropped by more than 10% compared to the previous day. Which visual enhancement would best highlight these underperforming regions without requiring manual data scanning?Visualize and analyze the data
  50. 100.A data analyst is building a Power BI report for a global sales team. The report needs to display sales performance across different countries. The sales director specifically requested a visual that can quickly show sales values directly on a geographic map, with countries colored according to their sales performance (e.g., darker shade for higher sales). Which Power BI visual is best suited for this requirement?Visualize and analyze the data