Your organization uses Microsoft Intune to manage devices. You need to ensure that Power BI reports can only be viewed on managed devices that are compliant with company policies. What should you configure?
A.Power BI Premium capacity setting 'Restrict access to mobile devices'.
B.Conditional Access policy in Microsoft Entra ID requiring device compliance.
C.Power BI tenant setting 'Require users to sign in with Microsoft Entra ID'.
D.Mobile app management (MAM) policy in Microsoft Intune.
AnswerB
Conditional Access can block access from non-compliant devices.
Why this answer
Option A is correct because conditional access policies in Microsoft Entra ID can require compliant devices. Option B is wrong because it doesn't enforce device compliance. Option C is wrong because it's for mobile app protection, not device compliance.
You have a column 'ProductID' that contains integers. You need to ensure that this column is used as a key in relationships. What data type should the column have?
A.Decimal Number.
B.Text.
C.Whole Number.
D.Binary.
AnswerC
Whole Number (integer) is optimal for key columns.
Why this answer
Option C is correct because in Power BI, relationship keys must be of a data type that supports exact matching and efficient indexing. Whole Number (integer) is the optimal type for primary keys in relationships, as it ensures unique, non-decimal values that Power BI can use for fast lookups and joins without precision issues.
Exam trap
The trap here is that candidates may think Text is always acceptable for keys (since it can hold any value), but the question specifically tests the optimal data type for integer-based keys in Power BI relationships, where Whole Number is the correct and most efficient choice.
How to eliminate wrong answers
Option A is wrong because Decimal Number introduces floating-point precision issues that can cause relationship failures or unexpected mismatches when exact key matching is required. Option B is wrong because Text can be used as a key, but it is less efficient than Whole Number and not the recommended type for integer-based IDs; the question specifies the column contains integers, so Text would require unnecessary conversion. Option D is wrong because Binary is not a valid data type for relationship keys in Power BI; it is used for storing binary data like images and cannot participate in relationships.
You see the above XMLA response when querying a Power BI dataset. You want to create a measure that calculates the difference between current sales and previous year sales, but you want to use the calculation group to avoid writing multiple measures. Which measure expression should you use?
This uses the calculation group to dynamically compute the difference.
Why this answer
With a calculation group, you can define a measure that references the calculation items. The expression 'SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name] = "PY")' will compute the difference between the current measure (e.g., Total Sales) and its previous year version.
You have a Power BI dataset that refreshes daily from an on-premises SQL Server database. The refresh fails with an error 'The data source credentials cannot be used for the connection'. What is the most likely cause?
A.The on-premises gateway is not running.
B.The dataset is using DirectQuery and the connection string is incorrect.
C.The credentials stored in the gateway for the SQL Server have changed or expired.
D.The SQL Server is not accessible from the cloud.
AnswerC
This is a common credential issue.
Why this answer
Option C is correct because the error 'The data source credentials cannot be used for the connection' specifically indicates that the credentials stored in the on-premises data gateway for the SQL Server data source have changed or expired. When a Power BI dataset refreshes from an on-premises SQL Server via a gateway, the gateway uses stored credentials to authenticate; if those credentials are no longer valid (e.g., password was rotated), the connection fails with this exact error.
Exam trap
The trap here is that candidates often confuse a credentials error with a connectivity error (like the gateway being offline or the server being unreachable), but the specific wording 'credentials cannot be used' points directly to authentication failure, not network or gateway availability.
How to eliminate wrong answers
Option A is wrong because if the on-premises gateway were not running, the error would typically be 'Unable to connect to the gateway' or 'Gateway not found', not a credentials-specific error. Option B is wrong because the dataset refreshes daily (implying Import mode, not DirectQuery), and even if it were DirectQuery, an incorrect connection string would produce a 'Cannot connect to server' or 'Login failed' error, not a credentials-specific error. Option D is wrong because if the SQL Server were not accessible from the cloud, the error would be a network-level timeout or 'Server not found', not a credentials-specific error; the gateway handles the cloud-to-on-premises connectivity, so the cloud does not directly access the SQL Server.
You need to design a report that allows users to drill from a yearly sales summary to quarterly details, then to monthly details, while maintaining the ability to go back. Which visual interaction should you enable?
A.Bookmarks
B.Tooltips
C.Drill-down
D.Drillthrough
AnswerC
Drill-down allows users to expand hierarchical levels with a back button.
Why this answer
Option B is correct because drill-down is the built-in Power BI feature that allows users to drill into hierarchical levels and use the back button to navigate up. Option A is wrong because drillthrough navigates to a separate page, not drill within the same visual hierarchy. Option C is wrong because tooltips provide additional detail on hover, not drill-down.
Option D is wrong because bookmarks capture a specific state, not hierarchical drill.
You are connecting to an Azure SQL database using DirectQuery. The database has a large table with millions of rows. Users need to see aggregated data quickly. What should you implement to improve query performance?
A.Create aggregations in Power BI on the large table.
B.Increase the memory limit of the Power BI Desktop.
C.Use a composite model with a smaller imported table.
D.Add indexes to the database table.
AnswerA
Aggregations reduce the amount of data queried from the source.
Why this answer
Option A is correct because creating aggregations in Power BI on the large table allows the DirectQuery model to pre-aggregate data at the source or in Power BI, reducing the volume of data queried and improving response times for aggregated results. This is a key performance optimization for DirectQuery models with large tables, as it avoids scanning millions of rows for every query.
Exam trap
The trap here is that candidates often confuse database-side optimizations (like indexes) with Power BI-side optimizations (like aggregations), leading them to choose Option D, but the question explicitly asks what you should implement in Power BI, not in the database.
How to eliminate wrong answers
Option B is wrong because increasing the memory limit of Power BI Desktop does not improve query performance against an Azure SQL database via DirectQuery; memory limits affect local processing, not the database query execution. Option C is wrong because using a composite model with a smaller imported table would break the DirectQuery requirement and introduce data freshness issues, as the imported table would need to be refreshed separately and may not reflect real-time data. Option D is wrong because adding indexes to the database table is a database-side optimization that can improve query performance, but it is not a Power BI implementation; the question asks what you should implement in Power BI, and indexes are managed by the database administrator, not within Power BI.
You are importing data from a SQL Server database into Power BI. The source table has a column 'OrderDate' of type DATETIME. You want to filter data based on the date only, ignoring time. What is the most efficient approach?
A.Change the data type of the OrderDate column to 'Date' in Power Query.
B.Create a calculated column in DAX using DATEVALUE(OrderDate).
C.Create a new column in Power Query using Date.From(OrderDate).
D.Use the 'Split Column' feature in Power Query to separate date and time.
AnswerB
This extracts the date part efficiently without altering the source column.
Why this answer
Option B is correct because DATEVALUE(OrderDate) in DAX converts a datetime value to a date-only value, which is the most efficient approach when you need to filter by date while keeping the original datetime column intact for other calculations. This avoids unnecessary data transformation in Power Query and leverages DAX's optimized date handling for filtering operations.
Exam trap
The trap here is that candidates often assume Power Query transformations are always more efficient than DAX calculated columns, but for simple date extraction, a DAX calculated column avoids data reloads and leverages the existing column's storage, making it the most efficient choice for filtering by date only.
How to eliminate wrong answers
Option A is wrong because changing the data type to 'Date' in Power Query modifies the source column permanently, which may lose time information needed for other analyses and requires reloading data if the original datetime is needed later. Option C is wrong because creating a new column in Power Query using Date.From(OrderDate) is functionally similar to Option A but adds an extra column, increasing data model size and processing overhead without performance benefit over a DAX calculated column. Option D is wrong because using 'Split Column' to separate date and time is inefficient and unnecessary, as it creates two columns and adds complexity, whereas a simple DAX calculated column achieves the same result with less overhead.
You are modeling data for a retail company. You have a 'Transactions' fact table with millions of rows. You need to improve query performance for a report that filters by 'Store' and 'Date'. Which action will have the most impact?
A.Merge the 'Store' and 'Transactions' tables into one.
B.Use a composite model with DirectQuery for the fact table.
C.Remove the date table and use the date column directly in the fact table.
D.Ensure the date table is marked as a date table and has a continuous date range.
AnswerD
Proper date table improves time intelligence and query performance.
Why this answer
Option C is correct because marking the date table improves time intelligence and performance. Option A is wrong because composite models add complexity. Option B is wrong because removing the date table would break time intelligence.
Option D is wrong because merging tables increases size.
You are reviewing the partition configuration for a Power BI Import model as shown in the exhibit. The table Sales is partitioned by year. You need to modify the model to improve incremental refresh performance. What change should you make?
A.Increase the number of partitions to monthly
B.Configure incremental refresh policy
C.Remove all partitions and load data as a single table
D.Change the storage mode to DirectQuery
AnswerB
Incremental refresh automatically creates and manages partitions based on a date range, optimizing refresh performance.
Why this answer
Configuring an incremental refresh policy (Option B) is the correct approach because it automatically manages partition creation and refresh for the Sales table based on a date/time column. This improves performance by refreshing only the most recent data (e.g., last 5 years) while keeping historical partitions unchanged, reducing refresh time and resource consumption compared to manual yearly partitions.
Exam trap
The trap here is that candidates may think increasing partition count (Option A) always improves performance, but in Power BI, too many partitions increase metadata overhead and refresh orchestration time, making incremental refresh policies the correct solution for efficient, automated partition management.
How to eliminate wrong answers
Option A is wrong because increasing partitions to monthly would create more granular partitions, which can actually degrade refresh performance due to overhead from managing many small partitions, and it does not address the need for incremental refresh logic. Option C is wrong because removing all partitions and loading data as a single table would force a full refresh of the entire Sales table every time, eliminating any performance gains from partitioning and incremental refresh. Option D is wrong because changing the storage mode to DirectQuery would bypass the Import model entirely, which is not an incremental refresh improvement and could introduce query performance issues due to live querying of the source.
Refer to the exhibit. A Power BI administrator is reviewing a dataset configuration exported from a workspace. The dataset connects to an on-premises SQL Server using Windows authentication. The administrator wants to configure a gateway to support scheduled refresh without storing credentials. Which gateway type and configuration should be used?
A.Use the on-premises data gateway (standard mode) and configure Kerberos constrained delegation for single sign-on
B.Use the on-premises data gateway (standard mode) and store the connection string in the gateway
C.Use the on-premises data gateway (personal mode) and store the Windows credentials in the gateway
D.Use the virtual network (VNet) gateway and configure a service principal
AnswerA
Kerberos delegation allows seamless Windows authentication without storing credentials.
Why this answer
Option D is correct because the on-premises data gateway supports Windows authentication via Kerberos constrained delegation, allowing refresh without storing credentials. Option A is wrong because the personal gateway is only for a single user and requires credentials. Option B is wrong because the VNet gateway is for Azure VNets, not on-premises.
Option C is wrong because the on-premises data gateway does not need a connection string stored in the gateway; it uses the dataset's connection string.
Your organization uses Power BI Premium per capacity. You need to ensure that only users from a specific Microsoft Entra ID security group can access the Power BI service. What should you do?
A.Disable the 'Publish to web' setting in the tenant admin portal.
B.Set the capacity to a different SKU.
C.Create a security group in the workspace and add users to that group.
D.Configure the Power BI tenant settings to 'Allow users to access Power BI service' and specify the security group.
AnswerD
This restricts access to the specified group.
Why this answer
Option A is correct because enabling tenant-level integration with Microsoft Entra ID allows restricting access to specific security groups. Option B is wrong because disabling public sharing doesn't restrict access. Option C is wrong because app security groups are for workspace access, not tenant access.
Option D is wrong because capacity settings control resources, not user access.
You are preparing data from a SQL Server database. The table 'Sales' contains a column 'OrderDate' that includes both date and time (e.g., '2023-10-15 14:30:00'). You need to create a separate column for the time portion only. Which TWO Power Query transformations can you use?
Select 2 answers
A.Extract - Duration
B.Extract - Year
C.Merge Columns
D.Format - Trim
E.Split Column by Delimiter (space)
AnswersA, E
Extracts time as duration from midnight.
Why this answer
Option A is correct because the 'Extract - Duration' transformation in Power Query extracts the time portion from a datetime column by calculating the duration since midnight, effectively isolating the time component. Option E is correct because splitting the column by a space delimiter separates the date and time parts into two columns, allowing you to keep only the time portion. Both methods produce a time-only value suitable for analysis.
Exam trap
The trap here is that candidates may think 'Extract - Duration' is only for calculating time differences, not for isolating the time portion, or they may overlook that splitting by a space delimiter is a valid alternative to more complex date/time functions.
Refer to the exhibit. You are reviewing the configuration of a Power BI dataset with row-level security (RLS). A user named 'user@contoso.com' reports that they can see all data when they should see only data for their region. What is the most likely cause?
A.The 'effectiveIdentity' is set to the user's email, bypassing RLS.
B.The RLS roles are missing the 'SalesRole' mapping to the dataset.
C.The dataset does not have any RLS roles defined.
D.The user is not assigned to any RLS role.
AnswerA
The effective identity setting forces the dataset to use that identity, ignoring RLS for that user.
Why this answer
Option C is correct because the 'effectiveIdentity' property in the exhibit is set to 'user@contoso.com', which overrides RLS and allows that user to see all data. In Power BI, the effective identity is used for testing but in production, it should not be set for a specific user if RLS is intended. Option A is wrong because the roles include users, so RLS is configured.
Option B is wrong because the exhibit does not indicate a missing role mapping. Option D is wrong because the roles are defined; the issue is the effective identity.
You have a Power BI data model with a table named Products that contains columns ProductID, ProductName, and CategoryID. You also have a table named Categories with CategoryID and CategoryName. You want to ensure that when a user filters by a category, all products in that category are shown, even if the product has no sales. What type of relationship should you create between Products and Categories?
A.Many-to-many
B.Many-to-one from Products to Categories
C.One-to-one
D.One-to-many from Categories to Products
AnswerD
This ensures that filtering on Categories brings all related products, including those with no sales.
Why this answer
Option D is correct because a one-to-many relationship from Categories to Products ensures that each category can be associated with multiple products, and when a user filters by a category, all products in that category are displayed regardless of whether they have sales. This is the standard star schema design where the dimension table (Categories) filters the fact table (Products) via a one-to-many relationship, preserving referential integrity and showing all products in the filtered category.
Exam trap
The trap here is that candidates often confuse the direction of the relationship (many-to-one vs. one-to-many) and incorrectly think that filtering works the same in both directions, but Power BI's default cross-filter direction is single, so only a one-to-many from the dimension to the fact ensures all products in a category are shown.
How to eliminate wrong answers
Option A is wrong because a many-to-many relationship would imply that multiple categories can be associated with multiple products in a non-unique way, which is not the case here (each product belongs to exactly one category) and would introduce unnecessary complexity and potential ambiguity in filtering. Option B is wrong because a many-to-one relationship from Products to Categories would reverse the filter direction, meaning filtering by category would not automatically show all products in that category unless cross-filter direction is set to both, which is not the default and can lead to unexpected behavior. Option C is wrong because a one-to-one relationship would require each product to have exactly one category and each category to have exactly one product, which is incorrect since a category can contain many products.
You need to create a date table in Power BI using DAX. Which function should you use to generate a continuous list of dates?
A.CALCULATE.
B.CALENDAR.
C.TODAY.
D.DATEVALUE.
AnswerB
Generates a table of dates between start and end.
Why this answer
The CALENDAR function in DAX generates a single-column table of dates that starts from a specified start date and ends at a specified end date, creating a continuous list of all dates in that range. This is the correct function to use when you need to create a date table for time intelligence calculations in Power BI.
Exam trap
The trap here is that candidates confuse CALCULATE (a context modifier) with CALENDAR (a table generator) because of the similar spelling, or they mistakenly think TODAY or DATEVALUE can produce a range of dates when they only return single values.
How to eliminate wrong answers
Option A is wrong because CALCULATE is a filter modifier function used to evaluate an expression in a modified filter context, not to generate a list of dates. Option C is wrong because TODAY returns only the current date as a scalar value, not a continuous list of dates. Option D is wrong because DATEVALUE converts a date string into a date/time value, but it does not generate a range of dates.
Which THREE factors should you consider when designing a star schema for a Power BI semantic model? (Select three.)
Select 3 answers
A.Fact tables should contain measures and foreign keys to dimension tables.
B.Dimension tables should contain descriptive attributes and be denormalized.
C.Fact tables should be normalized to reduce data duplication.
D.Use calculated columns in dimension tables to derive new attributes.
E.Avoid creating many-to-many relationships between dimensions.
AnswersA, B, E
This is the core of star schema design.
Why this answer
Options A, C, and D are correct. Star schema design emphasizes dimension tables with descriptive attributes, fact tables with measures and foreign keys, and avoiding many-to-many relationships. Option B is wrong because normalization is for OLTP, not for analysis.
Option E is wrong because calculated columns are generally not recommended for dimension tables.
Which TWO of the following are valid methods to handle slowly changing dimensions (SCD) Type 2 in Power Query? (Select two.)
Select 2 answers
A.Merge the dimension table with a fact table
B.Update the existing row with new attributes
C.Use a lookup table that tracks changes with start and end dates
D.Overwrite the existing attributes
E.Add a new row with a surrogate key and effective date range
AnswersC, E
A lookup table with date ranges can implement Type 2.
Why this answer
Options A and D are correct. Power Query can implement SCD Type 2 by adding a row for each change with effective dates, or by using a lookup approach with start and end dates. Option B is not a standard method.
Option C is SCD Type 1. Option E merges tables but not Type 2.
Refer to the exhibit. A Power BI developer wrote the DAX measure above to calculate year-over-year sales growth. When tested in a matrix visual with Year on rows and Month on columns, the measure returns incorrect results for the month of February in leap years. What is the likely cause?
A.SAMEPERIODLASTYEAR does not handle leap years correctly for February 29.
B.The measure should be a calculated column, not a measure.
C.The variable CurrentSales is not needed; use SUM directly.
D.The DIVIDE function returns an error when PreviousSales is zero.
AnswerA
SAMEPERIODLASTYEAR shifts by 365 days, which may map to a different date in leap years.
Why this answer
Option B is correct because SAMEPERIODLASTYEAR shifts the date context by exactly 365 days, which can cause issues in leap years for dates after February 28. Option A is incorrect because DIVIDE handles division by zero. Option C is incorrect because the measure is correctly defined as a measure.
Option D is incorrect because the variable is fine.
Which TWO of the following are true about the Power BI composite model?
Select 2 answers
A.Composite models do not support many-to-many relationships.
B.All tables in a composite model must use the same storage mode.
C.A composite model can combine DirectQuery and Import tables.
D.Relationships can be created between tables from different source groups.
E.Calculated tables are not supported in composite models.
AnswersC, D
This is a key feature of composite models.
Why this answer
Option B is correct: composite models allow mixing DirectQuery and Import. Option D is correct: relationships can be created between tables from different sources. Option A is incorrect because composite models can have multiple storage modes.
Option C is incorrect because calculated tables are limited. Option E is incorrect because composite models support many-to-many.
Which THREE actions can help optimize a Power BI report's performance? (Select THREE.)
Select 3 answers
A.Use calculated columns instead of measures for complex aggregations.
B.Reduce the number of visuals on a single page.
C.Import all columns from source tables to avoid missing data.
D.Use aggregations in the data source (e.g., pre-summarize tables).
E.Disable cross-highlighting and cross-filtering where not needed.
AnswersB, D, E
Fewer visuals reduce rendering load.
Why this answer
Options A, C, and D are correct. Option B is wrong because using many calculated columns can hurt performance. Option E is wrong because importing all columns increases model size and refresh time; only import needed columns.
You need to create a Power BI report that allows users to analyze sales data by multiple dimensions including date, product, and region. Which THREE features should you use to provide a good user experience?
Select 3 answers
A.Add slicers for date, product, and region.
B.Create bookmarks to switch between different report views.
C.Set up drill-through pages for detailed analysis.
D.Disable cross-filtering between visuals.
E.Use only default tooltips.
AnswersA, B, C
Slicers provide intuitive filtering.
Why this answer
Options A, C, and E are correct. A: Slicers allow users to filter data interactively. C: Drill-through enables navigation to detail pages.
E: Bookmarks can save and switch between views. Option B is wrong because disabling cross-filtering limits interactivity. Option D is wrong because tooltips are for additional info, not primary navigation.
Option C is correct because SUMMARIZE is a DAX function that creates a calculated table by grouping rows from a table (Sales) and adding a new column with an aggregate expression (SUM(Sales[Amount])). Option E is correct because CALENDARAUTO() returns a single-column table of dates, which is a valid calculated table definition in Power BI.
Exam trap
The trap here is that candidates confuse DAX table functions with SQL syntax or non-existent functions, and they may overlook that CALENDARAUTO() is a valid calculated table function, while DATATABLE is for static tables, not dynamic calculated tables.
Refer to the exhibit. You are a Power BI administrator reviewing a workspace capacity configuration in the Power BI admin portal. Based on the exhibit, which of the following is true?
A.The workspace has data classification set to 'General'.
B.The workspace is on a Premium capacity with ABFSSmall storage format.
C.The workspace uses a customer-managed encryption key.
D.The workspace allows data sources from SharePoint Online.
AnswerB
'ABFSSmall' is a valid Premium capacity storage format.
Why this answer
Option C is correct because 'ABFSSmall' is a valid Premium capacity storage format (Azure Blob File System Small). Option A is wrong because encryption is 'ServiceManaged', not 'CustomerKey'. Option B is wrong because there is no 'SharePoint' in allowedDataSources.
Option D is wrong because the data classification is set to 'Confidential', not 'General'.
You have a Power BI model with a fact table 'Sales' and a dimension table 'Product'. The Product table contains columns: ProductID, ProductName, Category, and Subcategory. You want to create a hierarchy for drill-down in reports: Category > Subcategory > ProductName. What is the correct way to define this hierarchy?
A.In Model view, right-click 'Category' > 'Create hierarchy', then add Subcategory and ProductName as levels.
B.In Power Query, merge the Category, Subcategory, and ProductName columns into one.
C.Create a calculated column using CONCATENATE to combine the levels.
D.In Report view, add all three columns to a visual's Values well.
AnswerA
This creates a proper hierarchy for drill-down.
Why this answer
Option A is correct because Power BI's Model view allows you to create a hierarchy by right-clicking a column (e.g., Category) and selecting 'Create hierarchy', then adding Subcategory and ProductName as child levels. This defines a natural drill-down path for visuals, enabling users to navigate from Category to Subcategory to ProductName without modifying the data model or using workarounds.
Exam trap
The trap here is that candidates often confuse flattening data (merging or concatenating) with creating a true hierarchy, leading them to choose options B or C, which break drill-down functionality.
How to eliminate wrong answers
Option B is wrong because merging columns in Power Query creates a single text field, destroying the individual column granularity and preventing proper drill-down behavior in visuals. Option C is wrong because a calculated column using CONCATENATE also flattens the hierarchy into a single string, losing the ability to drill down stepwise through distinct levels. Option D is wrong because adding all three columns to a visual's Values well does not create a hierarchy; it treats each column as a separate measure or axis, not a structured drill-down path.
You have a Power BI report that shows sales by region. Users report that the map visual is not displaying data for some countries. What is the most likely cause?
A.The geographic data is not categorized correctly in the Data pane.
B.The report page filter is excluding those countries.
C.The map visual is limited to 30 data points.
D.The map visual only supports US addresses.
AnswerA
Correct categorization is required for Bing Maps to recognize locations.
Why this answer
The most likely cause is that the geographic data is not categorized correctly in the Data pane. Power BI map visuals rely on the data category (e.g., Country, State, City) assigned to each field to correctly geocode and plot locations. If a field containing country names is left as 'Text' or 'Uncategorized', Power BI may fail to recognize the values as geographic entities, resulting in missing data points on the map.
Exam trap
The trap here is that candidates often assume a filter or data limit is the cause, but the core issue is the data category metadata, which is a subtle but critical setting in Power BI for map visuals.
How to eliminate wrong answers
Option B is wrong because a report page filter would affect all visuals on the page, not just the map, and users would typically notice missing data across the report, not solely on the map. Option C is wrong because the map visual (Bing Maps) does not have a hard limit of 30 data points; the limit applies to scatter charts and other visuals, not to map visuals. Option D is wrong because Power BI map visuals support addresses globally via Bing Maps geocoding, not just US addresses.
You need to combine data from two tables in Power Query that have the same columns but different row sets. Which operation should you use?
A.Union Queries
B.Group By
C.Append Queries
D.Merge Queries
AnswerC
Appends rows from second table to first.
Why this answer
Append Queries is the correct operation in Power Query when you need to combine two tables with identical columns but different row sets. It stacks the rows from the second table beneath the rows from the first table, effectively performing a union of the data. This is distinct from Merge Queries, which joins columns based on a key, and Group By, which aggregates data.
Exam trap
The trap here is that candidates confuse 'Merge Queries' (which adds columns) with 'Append Queries' (which adds rows), especially since the term 'Union' is commonly used in SQL but is not the exact Power Query operation name.
How to eliminate wrong answers
Option A is wrong because 'Union Queries' is not a native Power Query operation; the correct term is Append Queries, and while the concept is similar to a SQL UNION, the Power Query interface uses 'Append' to combine rows. Option B is wrong because Group By is used to aggregate rows based on a column, not to combine separate tables with the same schema. Option D is wrong because Merge Queries performs a join (like SQL JOIN) that adds columns from one table to another based on matching keys, not stacking rows.
Which THREE of the following are best practices when preparing data in Power BI for optimal performance?
Select 3 answers
A.Merge all tables into a single table for simplicity.
B.Create calculated columns instead of measures when possible.
C.Set correct data types for all columns.
D.Remove unnecessary columns and rows during import.
E.Use query folding to push transformations to the data source.
AnswersC, D, E
Proper data types improve storage and performance.
Why this answer
Setting correct data types for all columns (Option C) is a best practice because it ensures that Power BI uses the most efficient storage and processing methods. Incorrect data types can lead to increased memory usage, slower query performance, and inaccurate calculations, as Power BI must perform implicit conversions at query time.
Exam trap
The trap here is that candidates may think merging all tables simplifies the model (Option A) or that calculated columns are more efficient than measures (Option B), but Power BI's in-memory engine and query folding mechanics reward normalized star schemas and measure-based calculations for optimal performance.
You are a Power BI administrator. A user reports they cannot publish a report to a shared workspace because they receive an error 'You need at least a Contributor role to publish to this workspace.' The user is a member of a security group that has been assigned the Viewer role on the workspace. What should you do to allow the user to publish?
A.Grant the user Read permission on the report in the workspace.
B.Add the user as a Contributor directly to the workspace, or change the security group role to Contributor.
C.Change the user's role to Viewer on the workspace and ask them to use the 'Publish to web' option.
D.Create a new workspace and add the user as a Member.
AnswerB
Contributor role allows publishing. Adding the user directly overrides the group role.
Why this answer
Option C is correct because the Viewer role does not allow publishing; the user needs at least a Contributor role. Adding them directly as a Contributor overrides the group assignment. Option A is wrong because Power BI does not allow granting permissions on individual reports in a workspace.
Option B is wrong because Viewer allows viewing only. Option D is wrong because the app workspace already exists.
You are managing a Power BI deployment that uses Premium capacity. Users report that reports are loading slowly during peak hours. You need to identify whether the capacity is being overused. Which metric should you monitor?
A.Memory usage percentage.
B.Total dataset size in the workspace.
C.CPU high time percentage.
D.Average query execution time.
AnswerC
Correct. High CPU indicates capacity overload.
Why this answer
Option C is correct because CPU over-utilization is a key indicator of capacity stress in Premium. The 'CPU high' metric directly reflects processing load. Option A is wrong because query execution time is a result, not a direct capacity metric.
Option B is wrong because memory usage is important but CPU is more directly tied to responsiveness. Option D is wrong because dataset size is a design factor, not a real-time capacity metric.
A data model has a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. There is a 'Customers' table with columns: CustomerID, CustomerName. To analyze orders by customer, what is the best practice for modeling the relationship?
A.Create a one-to-many relationship from Customers to Orders with single direction.
B.Create a one-to-one relationship between Customers and Orders based on CustomerID.
C.Create an inactive relationship and use USERELATIONSHIP in measures.
D.Create a many-to-one relationship from Orders to Customers with both directions.
AnswerA
This is the standard star schema approach, allowing filters from Customers to propagate to Orders.
Why this answer
Option A is correct because in a star schema, the Customers table (dimension) should have a one-to-many relationship to the Orders table (fact) filtered from the dimension side. This single-direction filter propagation ensures that when a customer is selected, only their orders are shown, while preventing unwanted cross-filtering from orders back to customers. This is the standard best practice for modeling dimension-to-fact relationships in Power BI.
Exam trap
The trap here is that candidates often confuse the direction of the relationship (thinking the fact table should be on the 'one' side) or overcomplicate the model by using inactive relationships or bidirectional filtering when a simple single-direction one-to-many is the correct and efficient choice.
How to eliminate wrong answers
Option B is wrong because a one-to-one relationship between Customers and Orders would require each CustomerID to appear only once in Orders, which is unrealistic for a transactional fact table where one customer can have many orders. Option C is wrong because an inactive relationship with USERELATIONSHIP is only used when you need multiple relationships between the same two tables (e.g., OrderDate and ShipDate), not for the primary dimension-to-fact relationship which should always be active. Option D is wrong because a many-to-one relationship from Orders to Customers with both directions would create ambiguous cross-filtering and potential performance issues; bidirectional filtering is reserved for specific scenarios like many-to-many relationships, not for standard star schema modeling.
You are designing a Power BI semantic model for a retail company. You have a 'Products' table with columns: ProductID, ProductName, Category, UnitPrice. You need to ensure that when users filter by 'Category', they see only products belonging to that category. What should you set the 'Category' column's 'Data Category' property to?
A.Geography
B.Image URL
C.Category
D.Place
AnswerC
Category is the appropriate Data Category for product category fields.
Why this answer
The 'Data Category' property in Power BI is used to assign a semantic meaning to a column, such as 'Category', 'Geography', or 'Image URL', which helps Power BI apply appropriate default behaviors like grouping or filtering. Setting the 'Category' column's 'Data Category' to 'Category' ensures that when users filter by that column, Power BI treats it as a categorical field, displaying only products belonging to the selected category. This property does not affect the underlying data but informs the visualization layer how to interpret the column for better user experience.
Exam trap
The trap here is that candidates confuse the 'Data Category' property (which sets semantic metadata for visuals) with the 'Sort by Column' or 'Group' properties, mistakenly thinking it directly controls filtering logic rather than influencing how Power BI interprets the column for default visual behaviors.
How to eliminate wrong answers
Option A is wrong because 'Geography' is a data category intended for location-based columns (e.g., City, Country) and would not affect filtering behavior for product categories. Option B is wrong because 'Image URL' is used to display images from URLs in visuals, not to control filtering or grouping of categorical data. Option D is wrong because 'Place' is a specific data category for geographic places (e.g., addresses) and has no relevance to product category filtering.
Refer to the exhibit. You run this DAX query but the YoY Growth column returns blank for all rows. What is the most likely cause?
A.Local measures cannot be referenced by other local measures in SUMMARIZECOLUMNS
B.The subtraction operator does not work with measures
C.SAMEPERIODLASTYEAR requires a continuous date range
D.The 'Date' table has no relationship with Sales
AnswerA
This is a known limitation; define measures in the model or use CALCULATE.
Why this answer
The 'Total Sales' measure is defined locally (DEFINE), but SUMMARIZECOLUMNS may not evaluate local measures in the same context. The measure reference [Total Sales] inside SUMMARIZECOLUMNS might not resolve correctly. Moving the measure definition to the model would fix it.
C.Use star schema design with dimension and fact tables.
D.Use many-to-many relationships directly without bridge tables.
E.Use calculated columns instead of measures for aggregations.
AnswersA, B, C
Reduces model size and complexity.
Why this answer
Options A, C, and D are correct. Star schema design (A) reduces complexity. Avoiding bi-directional cross-filtering (C) prevents performance degradation.
Hiding unnecessary columns (D) reduces model size and improves clarity. Option B is incorrect because calculated columns are generally slower than measures for dynamic calculations. Option E is incorrect because using many-to-many relationships without bridge tables can cause ambiguity and poor performance.
You are preparing data for a Power BI report that analyzes customer churn. The source data contains the following columns: CustomerID, Churn (Yes/No), AgeGroup (Teen, Adult, Senior), SubscriptionType (Basic, Premium), MonthlyCharges, TotalCharges, TenureMonths. You need to ensure data quality and optimize the model. Which TWO actions should you take? (Choose two.)
Select 2 answers
A.Convert Churn column to a numeric 0/1 column.
B.Create a calculated column for AverageCharges per customer.
C.Remove TotalCharges column if it can be calculated from MonthlyCharges and TenureMonths.
D.Set CustomerID as a unique key and mark it as the primary column.
E.Create a separate table for subscription types.
AnswersC, D
Reduces model size and redundancy.
Why this answer
Option C is correct because removing derived columns like TotalCharges (which equals MonthlyCharges × TenureMonths) reduces data redundancy, improves model size, and avoids potential inconsistencies. Option D is correct because setting CustomerID as a unique key and marking it as the primary column ensures proper row-level identity, enables efficient relationships, and is a best practice for star schema modeling in Power BI.
Exam trap
Microsoft often tests the distinction between calculated columns and measures, leading candidates to choose Option B (creating a calculated column for an aggregation) instead of recognizing that such aggregations belong in measures to avoid bloating the model and breaking filter context.
You are creating a Power BI report that uses a table named Orders with columns: OrderID, OrderDate, ShipDate, and Status. You need to create a calculated table that contains one row per month with the total number of orders shipped in that month. Which TWO steps should you take?
Select 2 answers
A.Use CALENDARAUTO in a measure
B.Use the GROUPBY function
C.Create a date table using CALENDAR or CALENDARAUTO
D.Use SUMMARIZECOLUMNS with 'Date'[Month] and COUNTROWS of Orders
E.Use the VALUES function to get unique months
AnswersC, D
A date table is required for time intelligence functions.
Why this answer
The correct answers are A and D. First, create a date table (A) for time intelligence, then use SUMMARIZECOLUMNS (D) to group by month and count orders. Option B (CALENDARAUTO) is a function to create a date table, but the step is incomplete.
Option C (VALUES) returns distinct values, not aggregated. Option E (GROUPBY) is similar but less common.
Which TWO actions should you take to ensure that sensitive columns (e.g., customer names) are not visible to certain users in a Power BI report? (Select exactly two.)
Select 2 answers
A.Use object-level security (OLS) to hide the columns for specific roles.
B.Apply dynamic data masking on the dataset in Power BI service.
C.Use row-level security (RLS) to filter out rows containing sensitive data.
D.Create separate datasets with and without sensitive columns and assign permissions accordingly.
E.Set the report to not allow users with certain roles to view the report.
AnswersA, D
OLS can hide columns or tables.
Why this answer
Object-level security (OLS) can hide entire columns from roles. Row-level security (RLS) filters rows but cannot hide columns. Building separate datasets is one way but not efficient.
Option A and B are correct. Option C is wrong because RLS does not hide columns. Option D is wrong because that controls access to the report, not column visibility.
Option E is wrong because masking is not available in Power BI.
You need to create a relationship between two tables where one table has duplicate values on the key column. Which cardinality should you choose?
A.One-to-one
B.Many-to-many
C.Many-to-one
D.One-to-many
AnswerC
Correct when the many side (fact) has duplicates and the one side (dimension) has unique keys.
Why this answer
Option B is correct: Many-to-one is used when the lookup table has unique values and the fact table has duplicates. Option A is wrong because one-to-many is the reverse. Option C is wrong because many-to-many is for when both sides have duplicates.
Option D is wrong because one-to-one is for unique keys on both sides.
You have a Power BI model containing a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. You need to create a measure that calculates the total amount from the previous year. Which DAX function should be used?
A.DATEADD
B.PARALLELPERIOD
C.PREVIOUSYEAR
D.SAMEPERIODLASTYEAR
AnswerC
Returns the previous calendar year.
Why this answer
The PREVIOUSYEAR function is specifically designed to return a table of dates for the previous year relative to the current context in the filter context. It is the most direct and appropriate DAX function for calculating total amount from the previous year when working with a standard calendar date column.
Exam trap
The trap here is that candidates often confuse PREVIOUSYEAR with SAMEPERIODLASTYEAR, not realizing that SAMEPERIODLASTYEAR shifts the exact date range (e.g., same month last year) rather than returning the entire previous year.
How to eliminate wrong answers
Option A is wrong because DATEADD shifts dates by a specified interval (e.g., -1 year) but requires a continuous date range and can produce unexpected results if the date column has gaps. Option B is wrong because PARALLELPERIOD returns a parallel period of the same length (e.g., full year) but shifts the entire period, which may not align with a simple 'previous year' calculation when the current context is a month or quarter. Option D is wrong because SAMEPERIODLASTYEAR returns the exact same dates one year back, which is ideal for same-period comparisons (e.g., same month last year) but not for aggregating the entire previous year.
You are a Power BI administrator for a large enterprise. You have a shared dataset that is used by multiple reports. The dataset contains sensitive financial data. You need to implement row-level security (RLS) so that managers can only see data for their own region. The dataset has a table 'Sales' with RegionID, and a table 'Managers' with ManagerID, ManagerName, and RegionID. You need to create an RLS role that filters the Sales table based on the logged-in user's region. You have the following options: A. Create a role with a DAX filter: 'Sales[RegionID] = USERPRINCIPALNAME()'. B. Create a role with a DAX filter: 'Sales[RegionID] = LOOKUPVALUE(Managers[RegionID], Managers[ManagerName], USERPRINCIPALNAME())'. C. Create a role with a DAX filter: 'Sales[RegionID] = USERNAME()'. D. Create a role with a static filter for each region. Which option should you choose?
A.Create a role with a DAX filter: 'Sales[RegionID] = USERNAME()'
B.Create a role with a DAX filter: 'Sales[RegionID] = USERPRINCIPALNAME()'
C.Create a role with a static filter for each region
D.Create a role with a DAX filter: 'Sales[RegionID] = LOOKUPVALUE(Managers[RegionID], Managers[ManagerName], USERPRINCIPALNAME())'
AnswerD
This dynamically maps the user to their region.
Why this answer
Option B is correct because it uses LOOKUPVALUE to find the region for the logged-in user (ManagerName) from the Managers table, and then filters Sales accordingly. Option A is wrong because RegionID is not a user principal name. Option C is wrong because USERNAME() returns the user's login name, not region.
Option D is wrong because static filters would require multiple roles and manual maintenance.
You have a Power BI model with a table named 'Orders' that contains columns: OrderID, CustomerID, OrderDate, and TotalAmount. You need to create a measure that calculates the total sales amount for orders placed in the last 30 days, but only for customers who have placed more than 5 orders in total. What is the most efficient DAX measure?
This efficiently applies both filters using CALCULATE with KEEPFILTERS.
Why this answer
Option B is correct because it uses KEEPFILTERS to apply both the date filter and the customer-level condition within a single CALCULATE, ensuring that the row context for the customer filter is properly expanded via ALLEXCEPT without causing context transition issues. This approach is more efficient than using multiple FILTER iterators, as it leverages CALCULATE's filter modification capabilities to apply the conditions at the correct granularity.
Exam trap
The trap here is that candidates often choose Option A or C because they think multiple FILTER functions or SUMX are necessary for row-level conditions, not realizing that CALCULATE with KEEPFILTERS and ALLEXCEPT can apply both filters efficiently without nested iteration.
How to eliminate wrong answers
Option A is wrong because it uses two separate FILTER iterators over the Orders table, which forces a nested row context and can lead to incorrect results due to context transition; the second FILTER attempts to evaluate a CALCULATE with ALLEXCEPT inside a row context, which may not correctly count orders per customer. Option C is wrong because SUMX with a FILTER that includes a CALCULATE inside the logical expression causes context transition for each row, leading to poor performance and potentially incorrect customer-level aggregation; it also applies the date filter row-by-row rather than as a filter argument. Option D is wrong because it only filters by date using DATESINPERIOD and completely omits the customer condition (more than 5 orders), so it does not meet the requirement.
A Power BI report uses a composite model combining Import and DirectQuery sources. When a user filters a visual, the report takes a long time to update. The admin wants to diagnose performance issues. Which tool should the admin use?
A.DAX Studio.
B.Performance Analyzer in Power BI Desktop.
C.Power BI activity log.
D.On-premises data gateway log.
AnswerB
Performance Analyzer provides detailed timing for each visual and query.
Why this answer
Option C is correct because Performance Analyzer shows query durations for each visual, helping identify slow queries. Option A is wrong because DAX Studio analyzes DAX queries but not the full report performance. Option B is wrong because the activity log shows events but not query-level details.
Option D is wrong because the gateway log is for gateway activities.
Refer to the exhibit. You are configuring a Power BI data model for a workspace containing a semantic model with sensitive salary data. The JSON policy above is applied. A viewer reports that they can see the EmployeeSalaries table in the report. What is the most likely reason?
A.The EmployeeSalaries table is not in the semantic model.
B.The excludedTables property hides the table only in Power Query, not in reports.
C.The JSON policy is for row-level security (RLS), not object-level security (OLS).
D.The policy has not been applied to the semantic model.
AnswerC
RLS filters rows, not tables; OLS requires a different configuration.
Why this answer
Option C is correct because the JSON policy shown uses the `tables` array with `filter` expressions, which is the syntax for row-level security (RLS) policies in Power BI. RLS restricts data rows within a table but does not hide the table itself from the report view. Object-level security (OLS) requires a different policy structure using `excludedTables` or `setObjectLevelSecurity` in XMLA endpoints or Tabular Editor, not a JSON RLS policy.
Therefore, the viewer can still see the EmployeeSalaries table because OLS has not been configured.
Exam trap
The trap here is that candidates confuse row-level security (RLS) with object-level security (OLS), assuming any JSON policy that restricts data will also hide the table structure, but RLS only filters rows and does not prevent the table from appearing in the report.
How to eliminate wrong answers
Option A is wrong because if the EmployeeSalaries table were not in the semantic model, the viewer would not see it at all; the question states they can see it, so the table exists. Option B is wrong because the `excludedTables` property is not a Power Query concept; it is used in OLS policies to hide tables from reports, and the JSON shown does not contain `excludedTables`. Option D is wrong because the policy has been applied (the JSON is described as 'applied'), and the issue is not about application but about the type of security: RLS does not hide tables.
You are building a Power BI semantic model for a healthcare provider. The model must track patient visits and procedures. You have three source tables: Patients (PatientID, Name, DOB, Gender), Visits (VisitID, PatientID, VisitDate, DoctorID), Procedures (ProcedureID, VisitID, ProcedureCode, Cost). The model should allow users to analyze procedure costs by patient demographics (gender, age group) and by date (year, quarter, month). You need to design the star schema. Which of the following is the most appropriate design?
A.Use DirectQuery and create relationships between all three tables.
B.Create a separate model for visits and another for procedures, then combine them in a composite model.
C.Patients as a dimension, Visits as a fact table, Procedures as a fact table with a many-to-one relationship to Visits.
D.Merge all tables into a single flat table in Power Query.
AnswerC
This enables analysis of procedure costs by patient demographics and date.
Why this answer
Option A is correct: Patients as a dimension, Visits as a fact (or bridge), and Procedures as a fact. This allows cost analysis by patient attributes and date. Option B is wrong because merging all into one table leads to a wide table with redundancy.
Option C is wrong because creating separate models for visits and procedures prevents combined analysis. Option D is wrong because using DirectQuery for all tables can cause performance issues.
You have a Power BI report that uses a composite model with a DirectQuery source (SQL Server) and a local imported table. The report includes a measure that calculates sales growth by comparing current year to previous year. Users report that the measure returns blank for months with no data in the current year. How should you modify the measure to show 0% growth instead of blank?
A.Use ISBLANK(CurrentYear) in an IF statement.
B.Add a condition to check if PreviousYear = 0.
C.Use DIVIDE with an alternate result of 0.
D.Use COALESCE(CurrentYear, 0) in the measure.
AnswerD
COALESCE replaces blank with 0, allowing DIVIDE to compute 0% growth.
Why this answer
Option B is correct because COALESCE replaces blank with the next non-blank value, so if CurrentYear is blank but PreviousYear has data, it returns 0. Option A is wrong because ISBLANK would still return blank if CurrentYear is blank. Option C is wrong because it only handles division by zero.
Option D is wrong because it replaces blank with 0 only for the denominator.
You need to enforce that only users with a specific sensitivity label can access a Power BI report. Which TWO actions should you take?
Select 2 answers
A.Assign users to the Viewer role in the workspace.
B.Enable sensitivity labels for Power BI in Microsoft Purview.
C.Configure row-level security (RLS) on the dataset.
D.Set the default sensitivity label for the workspace.
E.Create a label-based access control policy in Microsoft Purview.
AnswersB, E
Required to use labels in Power BI.
Why this answer
Options A and C are correct. Sensitivity labels must be enabled for Power BI via Microsoft Purview, and then you can restrict access using label-based policies. Option B is wrong because row-level security controls data, not access to the report.
Option D is wrong because workspace roles do not consider sensitivity labels. Option E is wrong because that setting controls default label, not access restriction.
You are configuring row-level security (RLS) for a Power BI dataset. The sales data includes a 'Region' column. You want users in the 'Sales Managers' group to see data only for their assigned region, which is stored in a separate mapping table. You create the following DAX filter: [Region] = LOOKUPVALUE(EmployeeRegion[Region], EmployeeRegion[UserPrincipalName], USERPRINCIPALNAME()). When testing, a user in 'Sales Managers' sees no data. What is the most likely issue?
A.The LOOKUPVALUE function returns multiple values for the user, causing an error.
B.The USERPRINCIPALNAME function is not supported in RLS filters.
C.The DAX filter syntax is incorrect; it should use CALCULATE.
D.RLS requires a Premium capacity license.
AnswerA
Correct. Duplicate entries cause LOOKUPVALUE to fail.
Why this answer
Option C is correct because LOOKUPVALUE returns an error if multiple values are found (e.g., duplicate user entries in the mapping table), causing the filter to fail and return no data. Option A is wrong because RLS does not require Premium. Option B is wrong because the USERPRINCIPALNAME function is correct for Power BI Service.
Option D is wrong because the DAX itself is syntactically correct; the issue is with data.
You are connecting Power BI to an Azure SQL Database. You need to ensure that the connection uses the most secure authentication method available. Which THREE options should you consider? (Choose three.)
Select 3 answers
A.Use Windows authentication.
B.Use SQL Server authentication with a strong password.
C.Use a service principal with Microsoft Entra ID.
D.Use Microsoft Entra ID (formerly Azure AD) authentication.
E.Use a managed identity for the Power BI service.
AnswersC, D, E
Service principals are used for automated scenarios and are secure.
Why this answer
Option C is correct because using a service principal with Microsoft Entra ID (formerly Azure AD) enables token-based authentication for Power BI to Azure SQL Database, which is more secure than shared secrets or passwords. Service principals are non-interactive identities that can be granted fine-grained permissions, eliminating the need to store credentials and reducing the risk of credential exposure.
Exam trap
The trap here is that candidates often assume Windows authentication is the most secure option for Azure SQL Database, but it is not supported in Azure; the correct secure methods rely on Microsoft Entra ID tokens, service principals, or managed identities.
You are a Power BI developer at a retail company. You have a data model with a 'Sales' fact table (10 million rows) and dimension tables: 'Date', 'Customer', 'Product', 'Store'. The 'Sales' table includes columns: SalesID, DateKey, CustomerID, ProductID, StoreID, Quantity, UnitPrice, Discount, TotalAmount. The 'Product' dimension has 5,000 rows and includes columns: ProductID, ProductName, Category, SubCategory, Brand, Price. The 'Store' dimension has 200 rows and includes columns: StoreID, StoreName, Region, City, Manager. The 'Customer' dimension has 100,000 rows. The report currently has a measure 'Total Sales' = SUM(Sales[TotalAmount]) and a measure 'Total Quantity' = SUM(Sales[Quantity]). Users complain that the report is slow when filtering by multiple categories and regions simultaneously. You need to improve performance without changing the data source. Which action should you take first?
A.Create an aggregation table for the Sales table
B.Disable the auto date/time feature in Power BI
C.Change the storage mode of the Sales table to Dual
D.Reduce the number of columns in the Customer dimension by removing unused columns
AnswerD
Removing unnecessary columns reduces model size and improves performance.
Why this answer
Option C is correct because reducing the cardinality of the Customer dimension by removing unnecessary columns (e.g., address details) can significantly reduce model size and improve performance. Option A is wrong because disabling auto date/time might help but is a minor improvement. Option B is wrong because aggregations can help but require additional setup and are not the first step.
Option D is wrong because changing to dual storage mode may not improve performance and could increase complexity.
Which TWO are valid ways to combine data from multiple sources in Power Query? (Choose two.)
Select 2 answers
A.Pivot Column.
B.Append Queries.
C.Create relationships in the data model.
D.Merge Queries.
E.Group By.
AnswersB, D
Combines tables by stacking rows.
Why this answer
Option B is correct because the Append Queries operation in Power Query combines rows from two or more tables by stacking them vertically, which is a standard way to consolidate data from multiple sources when the columns are aligned. This is distinct from merging, which combines columns based on matching keys.
Exam trap
The trap here is that candidates often confuse 'combining data from multiple sources' with 'creating relationships in the data model,' which is a separate step performed after data loading, not a Power Query transformation.
You are reviewing the M query snippet above. The query imports an Excel file and filters rows where Date is on or after January 1, 2024. However, the query fails with an error. What is the most likely cause?
A.The data type conversion cannot handle null values.
B.The Excel.Workbook function cannot read .xlsx files.
C.The date filter syntax is incorrect.
D.The file path is local and may not be accessible after deployment.
AnswerD
Hardcoded local paths cause failures when the file is not available.
Why this answer
Option B is correct because the file path is hardcoded to a local drive, which may not exist on the Power BI Service or another machine. Option A is wrong because the date filter syntax is correct. Option C is wrong because the Excel.Workbook function handles .xlsx files.
Option D is wrong because the data type transformations are correct.
You are a Power BI administrator. A user reports that they cannot see any data when they open a report that uses row-level security (RLS). The user is a member of the 'Sales' RLS role, which filters data to [Region] = 'North America'. However, the report shows blank visuals. What is the most likely cause?
A.The user does not have permission to view the report.
B.The user is also a member of a workspace role that overrides RLS.
C.The RLS role was defined in the Power BI service after publishing, but the user was not added.
D.The RLS filter uses an exact string match, but the actual region values in the data are different (e.g., 'North America' vs 'North America ' or 'NA').
AnswerD
A mismatch in string values would result in no data being returned.
Why this answer
Option D is correct because if the 'North America' region name in the data does not exactly match the filter (e.g., 'North America' vs 'NA'), the filter will not match any rows. Option A is wrong because the user can see the report, just no data. Option B is wrong because RLS roles are usually defined in Desktop.
Option C is wrong because the user is in the Sales role, so they should see data.
Your company uses Microsoft Purview Information Protection sensitivity labels. You publish a Power BI dataset that includes columns containing personally identifiable information (PII). You want to automatically apply sensitivity labels based on column sensitivity. What should you configure?
A.Configure an auto-labeling policy in Microsoft Purview.
B.Use Microsoft Defender for Cloud Apps session controls.
C.Apply a sensitivity label to the dataset manually in Power BI Service.
D.Enable Microsoft Sentinel to label Power BI assets.
AnswerA
Auto-labeling can scan and label based on sensitive data patterns.
Why this answer
Option A is correct because auto-labeling policies in Microsoft Purview can scan and label Power BI datasets. Option B is wrong because Microsoft Defender for Cloud Apps applies labels based on app-level behavior, not column data. Option C is wrong because sensitivity labels in Power BI are manual or inherited from data sources.
Option D is wrong because Microsoft Sentinel is for security information and event management.
You are modeling data for a sales analysis. You have a fact table 'Sales' and a dimension table 'Date'. You need to create a relationship between 'Sales[OrderDate]' and 'Date[Date]'. The 'Date' table contains dates from January 1, 2020 to December 31, 2025. The 'Sales' table contains orders from January 1, 2021 to June 30, 2024. Which relationship cardinality should you use?
A.Many-to-many (*:*)
B.One-to-many (1:*)
C.Many-to-one (*:1)
D.One-to-one (1:1)
AnswerC
Many sales rows for one date, and the Date table has unique dates.
Why this answer
The correct cardinality is Many-to-one (*:1) because the 'Sales' table contains many rows for each date (multiple orders can occur on the same date), while the 'Date' table has unique dates. This creates a classic star-schema relationship where the many side (Sales) points to the one side (Date). In Power BI, the cardinality is defined from the perspective of the filter propagation direction, so *:1 means many rows in Sales match one row in Date.
Exam trap
The trap here is that candidates often confuse the direction of the cardinality notation (1:* vs *:1) and incorrectly select One-to-many (1:*) because they think the 'one' side should be the dimension table, but Power BI's cardinality is defined from the filter context perspective, not the table roles.
How to eliminate wrong answers
Option A is wrong because Many-to-many (*:*) would imply that multiple rows in Sales can match multiple rows in Date and vice versa, which is not the case here since Date has unique dates and Sales has one OrderDate per row. Option B is wrong because One-to-many (1:*) would require the 'one' side to be Sales and the 'many' side to be Date, but Sales has many orders per date while Date has unique dates, so the cardinality must be reversed. Option D is wrong because One-to-one (1:1) would require each order date in Sales to correspond to exactly one unique date in Date and each date in Date to correspond to exactly one order in Sales, which is false since multiple orders can occur on the same date.
You are designing a Power BI report for a sales team. They need to see their individual performance compared to team averages. You have a table with columns: Salesperson, Region, SalesAmount. Which visual best allows each salesperson to see their own value alongside the average?
A.Clustered bar chart with a measure for average
B.Waterfall chart
C.Table with conditional formatting
D.Combo chart (clustered column and line) with salesperson bars and average line
AnswerD
Allows easy comparison of individual to average.
Why this answer
Option B is correct because a combo chart can show individual bars and a line for average. Option A is wrong because a table with conditional formatting doesn't show average easily. Option C is wrong because a clustered bar chart can show average but not as clearly.
Option D is wrong because a waterfall chart is for cumulative changes.
Your Power BI dataset uses a SQL view that joins multiple tables. You notice that some columns have null values where you expect data. You suspect the view definition has a bug. How can you verify the view's output in Power Query?
A.Check the 'Table Preview' in the data model
B.Create a new query that runs the view's SQL directly against the source
C.Use 'View Native Query' in Power Query
D.Use 'Data Profiling' in Power Query
AnswerB
This allows you to see the view's output independently.
Why this answer
Option B is correct because creating a new query that runs the view's SQL directly against the source in Power Query allows you to isolate and execute the exact SQL statement, bypassing any transformations or folding issues. This lets you compare the raw output from the source with the view's expected results, directly verifying if the view definition itself contains a bug. It is the most straightforward method to confirm whether the null values originate from the view or from subsequent Power Query steps.
Exam trap
The trap here is that candidates confuse 'View Native Query' (which shows the folded query after transformations) with the ability to run the original view SQL directly, leading them to choose option C instead of B.
How to eliminate wrong answers
Option A is wrong because the 'Table Preview' in the data model shows data after all Power Query transformations have been applied, not the raw output of the SQL view; it cannot isolate the view's definition from subsequent data shaping steps. Option C is wrong because 'View Native Query' in Power Query displays the query that Power Query sends to the source after folding, which may include transformations and not the original view SQL; it does not let you run the view's SQL independently to verify its output. Option D is wrong because 'Data Profiling' in Power Query provides statistics like column quality and distribution, but it does not show the raw SQL output or allow you to execute the view's SQL directly to identify bugs in the view definition.
You have a Power BI report that uses a map visual to show store locations. Some stores have the same latitude and longitude. You want to distinguish overlapping points. Which visual feature should you use?
A.ArcGIS map
B.Bubble map with bubble size set to a measure
C.Filled map
D.Shape map
AnswerB
Bubble size can differentiate overlapping points.
Why this answer
Option B is correct because the bubble map with size can differentiate overlapping points by a measure. Option A is wrong because a shape map shows regions. Option C is wrong because a filled map shows regions.
Option D is wrong because arcGIS map may have similar issues.
Which THREE of the following are best practices for optimizing data load performance in Power BI?
Select 3 answers
A.Remove unnecessary columns and rows during the import process.
B.Split a large fact table into multiple smaller fact tables.
C.Set data types correctly in Power Query to avoid type detection overhead.
D.Use DirectQuery mode instead of Import mode to reduce data load time.
E.Use query folding to push transformations to the source database.
AnswersA, C, E
Reducing data volume improves load time.
Why this answer
Option A is correct because removing unnecessary columns and rows during the import process reduces the amount of data loaded into the Power BI data model, which directly decreases memory usage and refresh time. By filtering out irrelevant data early in Power Query, you minimize the data volume that must be processed and stored, leading to faster load performance.
Exam trap
The trap here is that candidates may confuse 'splitting tables' (Option B) with star schema design best practices, but splitting a fact table unnecessarily violates dimensional modeling principles and harms performance, whereas proper star schema involves splitting dimensions from facts, not splitting facts themselves.
Refer to the exhibit. A user is a member of both 'HRManager' and 'Executive' RLS roles. The dataset uses DirectQuery. When the user views a report showing all employees, what data will they see?
A.All rows in the Employees table.
B.Only rows where Department is both 'HR' and 'Executive' (intersection).
C.No rows because roles conflict.
D.Rows where Department is 'HR' or 'Executive' (union).
AnswerD
Multiple roles are additive with OR logic.
Why this answer
Option B is correct because in Power BI, when a user belongs to multiple RLS roles, the filters are combined using OR logic, so the user sees rows satisfying either role. Option A is wrong because it would require intersection (AND). Option C is wrong because all rows are not returned.
You are preparing data for a Power BI report. The source data contains a column with values like '1,234.56' formatted as text. You need to convert this to a numeric value for calculations. What is the best approach?
A.In Power Query Editor, split the column by comma and then use the second part.
B.In DAX, create a calculated column using VALUE() after removing commas.
C.In Power Query Editor, replace the comma with an empty string, then change the data type to Decimal Number.
D.In Power Query Editor, use the 'Clean' transform to remove non-numeric characters.
AnswerC
This removes the formatting and converts to number.
Why this answer
Option C is correct because Power Query Editor provides the most efficient and scalable method for cleaning and converting text-based numeric data. By replacing the comma with an empty string and then changing the column data type to Decimal Number, you perform the transformation directly in the data preparation layer (M language), which is optimized for performance and avoids the overhead of DAX calculated columns. This approach also ensures the data remains clean for all downstream calculations.
Exam trap
Microsoft often tests the misconception that the 'Clean' transform removes all non-numeric characters, but in reality it only removes non-printable control characters, not punctuation like commas or periods.
How to eliminate wrong answers
Option A is wrong because splitting the column by comma would separate the thousands separator from the number, leaving only the decimal part (e.g., '1' and '234.56'), which loses the integer portion and corrupts the value. Option B is wrong because using DAX with VALUE() after removing commas requires a calculated column that is evaluated row-by-row in the data model, which is less efficient than performing the transformation in Power Query and can lead to performance issues with large datasets. Option D is wrong because the 'Clean' transform in Power Query removes non-printable characters (like tabs and line breaks), not punctuation such as commas, so it would not remove the thousands separator and would leave the text value unchanged.
You have a table with a column 'Date' and a measure 'Total Sales'. You want to calculate the cumulative total of sales over time. Which DAX function should you use?
A.SUM(Sales[Amount])
B.DATESYTD('Date'[Date])
C.CALCULATE(SUM(Sales[Amount]), ALL(Sales))
D.TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
AnswerD
Calculates year-to-date cumulative sum.
Why this answer
Option C is correct because TOTALYTD calculates year-to-date cumulative totals. Option A is wrong because SUM adds all values, not cumulative. Option B is wrong because CALCULATE modifies filter context but does not inherently provide cumulative.
Option D is wrong because DATESYTD returns a set of dates, not a cumulative sum.
You are importing data from a CSV file that contains a column 'Date' with values in different formats (e.g., '01/15/2024', '2024-01-15'). You need to ensure all dates are recognized correctly. What should you do in Power Query?
A.In Power Query, set the data type to 'Date' and use 'Using Locale' to specify the correct locale for each file.
B.Change the data source to a SQL database.
C.Split the column into multiple columns and merge them back.
D.Use 'Replace Values' to standardize the date formats.
AnswerA
Locale settings handle different date formats.
Why this answer
Option A is correct because Power Query's 'Using Locale' feature allows you to specify the cultural context (e.g., 'en-US' for MM/DD/YYYY or 'de-DE' for DD.MM.YYYY) when setting a column's data type to 'Date'. This ensures that dates in various formats, such as '01/15/2024' (US) and '2024-01-15' (ISO 8601), are interpreted correctly during import, as the locale dictates the expected date order and separator.
Exam trap
The trap here is that candidates often assume 'Replace Values' or manual splitting can handle date format inconsistencies, but Power Query's locale-aware type detection is the only built-in method that correctly interprets ambiguous date strings like '01/15/2024' versus '15/01/2024' without data loss.
How to eliminate wrong answers
Option B is wrong because changing the data source to a SQL database does not solve the problem of inconsistent date formats in the CSV; it merely shifts the data ingestion point without addressing the parsing logic. Option C is wrong because splitting and merging columns is a manual, error-prone workaround that does not leverage Power Query's built-in locale-aware date parsing, and it would require complex transformations for each format variant. Option D is wrong because 'Replace Values' is designed for static text replacement, not for interpreting and standardizing date formats; it cannot handle the semantic conversion of '01/15/2024' to a date value.
Your organization uses Microsoft Purview to catalog Power BI assets. You need to ensure that all published reports and dashboards are automatically scanned and added to the catalog. What should you do?
A.Register the Power BI tenant as a data source in Microsoft Purview.
B.Enable 'Allow Azure Active Directory authentication' in the Power BI admin portal.
C.Apply sensitivity labels to all assets.
D.Create a new workspace in Power BI and assign the catalog admin role.
AnswerA
Registering the tenant enables automatic scanning.
Why this answer
Option B is correct. Registering a Power BI tenant as a data source in Purview enables automatic scanning of all Power BI assets. Option A is wrong because sensitivity labels help with classification but do not automatically add assets to the catalog.
Option C is wrong because enabling 'Allow Azure Active Directory (now Microsoft Entra ID) authentication' is not required for scanning. Option D is wrong because creating a new workspace does not trigger scanning.
You are reviewing a Power BI data source configuration JSON. The exhibit shows a data source definition. What is the privacy level setting for the data source 'SalesData'?
A.Organizational
B.Private
C.None
D.Public
AnswerB
The JSON explicitly sets privacyLevel to Private.
Why this answer
The privacy level setting for the data source 'SalesData' is 'Private' because the JSON snippet shows the property "privacyLevel":"Private". In Power BI, the Private privacy level restricts data from being combined with data from other sources, ensuring that sensitive data is not inadvertently shared across queries. This setting is explicitly defined in the data source configuration and is not Organizational, None, or Public.
Exam trap
The trap here is that candidates may confuse the 'Private' privacy level with the 'Organizational' level, thinking that data within the same organization is automatically safe to combine, but the JSON explicitly shows 'Private', which is the most restrictive setting.
How to eliminate wrong answers
Option A is wrong because 'Organizational' is a valid privacy level in Power BI that allows data to be combined with other Organizational data sources, but the JSON explicitly shows 'Private', not 'Organizational'. Option C is wrong because 'None' is not a valid privacy level in Power BI; the valid levels are Private, Organizational, and Public. Option D is wrong because 'Public' is a valid privacy level that allows data to be combined with any other data source, but the JSON explicitly shows 'Private', not 'Public'.
You are a Power BI administrator. A user in the Sales department needs to create reports using a shared dataset, but should not be able to modify the dataset or share it with others. What is the minimum permission level you should assign to the user on the dataset?
A.Reshare
B.Build
C.Write
D.Read
AnswerB
Build permission allows users to create reports based on the dataset without modifying or sharing it.
Why this answer
Option C is correct because 'Build' permission allows a user to create reports based on a dataset without being able to modify or share the dataset. Option A is wrong because 'Read' permission only allows viewing reports, not building new ones. Option B is wrong because 'Reshare' is not a standalone permission; sharing is part of the 'Share' permission.
Option D is wrong because 'Write' would allow modifications.
An administrator wants to monitor all Power BI activities, including viewing reports, exporting data, and sharing workspaces. Which tool should they use?
A.Power BI activity log in the admin portal
B.Power BI usage metrics report
C.Power BI capacity metrics app
D.Microsoft 365 audit log
AnswerA
The activity log records detailed user actions.
Why this answer
Option A is correct because the Power BI activity log captures user activities such as viewing, exporting, and sharing. Option B is wrong because the usage metrics report shows aggregate usage, not detailed activities. Option C is wrong because Microsoft 365 audit log is broader but less specific to Power BI; the activity log is the dedicated tool.
Option D is wrong because the admin portal's capacity metrics focus on performance.
You are using Power Query to combine data from multiple Excel files in a SharePoint folder. Each file has a sheet named 'Sales'. The columns across files are identical but occasionally a file has extra columns. You need to ensure the combined table contains only the common columns across all files. Which Power Query step should you use?
A.Use Table.Combine with the 'Table1' parameter
B.Use Remove Other Columns on the first file's columns
C.Use Table.ColumnsOfType to filter common columns
D.Use Merge Queries as New
AnswerC
Table.ColumnsOfType returns columns that exist in all tables.
Why this answer
Option D is correct because Table.ColumnsOfType allows selecting only columns that appear in all tables. Option A is wrong because Table.Combine appends all columns, creating nulls for missing columns. Option B is wrong because Merge queries are for joining, not combining.
Option C is wrong because Remove Other Columns removes columns not selected; you need to identify common ones first.
Which THREE factors should you consider when choosing between Import and DirectQuery storage modes? (Choose three.)
Select 3 answers
A.User license type (Pro vs Premium).
B.Report interactivity and performance needs.
C.Report color scheme and branding.
D.Data volume and size limits.
E.Data refresh frequency and latency requirements.
AnswersB, D, E
Import is faster for interactive reports; DirectQuery may be slower.
Why this answer
Option B is correct because Import mode pre-aggregates data into an in-memory columnstore, enabling sub-second DAX query responses, while DirectQuery mode sends queries to the source database in real time, which can introduce latency. The choice between them directly impacts report interactivity and performance, as Import mode is optimized for fast, interactive slicing and dicing, whereas DirectQuery may suffer from slower response times on complex measures or large result sets.
Exam trap
The trap here is that candidates confuse licensing constraints (Pro vs Premium) with storage mode capabilities, when in fact both modes are available regardless of license, though Premium offers higher Import size limits and additional features like automatic page refresh for DirectQuery.
Which THREE considerations are important when designing a dashboard for executive stakeholders?
Select 3 answers
A.Focus on key performance indicators (KPIs) that align with business goals.
B.Use consistent colors and fonts to maintain a professional look.
C.Provide high-level summaries with the ability to drill down if needed.
D.Include as many interactive slicers as possible for flexibility.
E.Use detailed tables to show all underlying data.
AnswersA, B, C
KPIs provide quick insight into performance.
Why this answer
Options B, D, and E are correct. B: KPIs allow quick performance assessment. D: High-level summaries prevent information overload.
E: Consistent design improves readability. A is wrong because interactivity can distract. C is wrong because dashboards should be concise, not detailed.
Which TWO of the following are valid DAX functions for time intelligence? (Select two.)
Select 2 answers
A.RANKX
B.DATEADD
C.CONCATENATEX
D.MINX
E.TOTALYTD
AnswersB, E
DATEADD shifts dates by an interval.
Why this answer
DATEADD is a valid DAX time intelligence function that shifts dates forward or backward by a specified number of intervals (days, months, quarters, years). TOTALYTD is also a valid time intelligence function that calculates the year-to-date value of an expression. Both are part of the dedicated time intelligence function set in DAX, which requires a properly marked date table with continuous dates.
Exam trap
Microsoft often tests the distinction between iterator functions (like RANKX, MINX, CONCATENATEX) and dedicated time intelligence functions (like DATEADD, TOTALYTD), causing candidates to confuse functions that perform row-by-row operations with those that manipulate date ranges.
You are reviewing a Power BI dataset schema. The Date column is used in time intelligence measures. What is missing from this schema?
A.There is no separate date table marked as a date table.
B.The version should be 2.0.
C.The Amount column should be a decimal.
D.The data type for Date should be date instead of dateTime.
AnswerA
Time intelligence requires a date table.
Why this answer
Option D is correct because a separate date table is needed for time intelligence functions to work properly. Option A is wrong because data types are specified. Option B is wrong because the schema is valid JSON.
Option C is wrong because there is no indication of an issue with columns.
You need to ensure that only members of the 'Sales' security group can edit reports in the 'Sales Reports' workspace. The workspace currently has four members: three from Sales and one from Marketing. What should you do?
A.Add the Sales security group as a workspace Admin and remove the Marketing user.
B.Configure the workspace to allow only specific users to edit by using the 'Restrict editing' option.
C.Publish the reports to a Power BI app and grant the Sales group 'Build' permissions.
D.Assign the Sales security group the 'Contributor' role and keep the Marketing user as 'Member'.
AnswerA
Adding the Sales group with Admin role grants edit access, and removing the Marketing user prevents their access.
Why this answer
Option C is correct because by assigning the 'Sales' group as the workspace admin or contributor, you can control access. However, the scenario requires that only Sales members can edit; the Marketing member must be removed or assigned a lower role. Removing the Marketing user from the workspace ensures they cannot edit.
Option A is wrong because adding the Sales group as Admin does not automatically remove the Marketing user. Option B is wrong because workspace roles cannot be assigned per user role; they are workspace-wide. Option D is wrong because app permissions do not control editing access.