Model the data practice questions

CALCULATE(SUM(Sales[Amount]), DATESMTD(Date[Date]))

Why it fails: Incorrect. DATESMTD returns a set of dates from the start of the month to the last date in the filter context, not the full previous month. This calculates month-to-date sales rather than total sales for the previous month.

CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Date[Date]))

Why it fails: Incorrect. SAMEPERIODLASTYEAR returns dates from the same period in the previous year, not the previous month. This would calculate sales from the same month last year, not the previous month.

CALCULATE(SUM(Sales[Amount]), DATEADD(Date[Date], -1, MONTH))

Why it fails: Incorrect. While DATEADD with -1 month shifts dates back one month, it can produce incorrect totals when the current filter context does not represent a full month (e.g., a range of dates within a month). PREVIOUSMONTH is more reliable for returning the entire prior month regardless of the current selection.

Configure bidirectional cross-filtering between all dimension tables and the fact table.

Why it fails: Setting all relationships to bidirectional makes filters flow both ways, so filtering a dimension filters another dimension through the fact table; with multiple dimensions this can create multiple paths between any two dims, leading to ambiguous row contexts and unexpected results. It also increases the number of join operations evaluated during query execution, degrading performance, especially at higher cardinalities. In a star schema for sales, you should keep relationships single-direction (from dimension to fact) and only use bidirectional in specific, controlled many-to-many or limited cases.

Use multiple inactive relationships between fact and dimension tables to support different analyses.

Why it fails: In a sales model, you might need different date roles (order date, ship date, due date). Using multiple inactive relationships forces you to explicitly activate them in each measure with CALCULATE and USERELATIONSHIP, adding complexity and making DAX less readable and harder to maintain. Also, only one relationship can be active between two tables, so having many inactive versions means you can't have default filtering; it's better to use a role-playing dimension approach or separate fact tables if needed. For typical sales analysis, keep one active relationship per dimension and use a dedicated date table.

Use many-to-many relationships between dimension tables and the fact table to simplify the model.

Why it fails: Sales fact tables often have natural many-to-many relationships (e.g., a product can belong to many categories, a customer to many segments), but creating many-to-many relationships in the model (without bridge tables) causes ambiguous aggregation and inflated numbers because DAX doesn't know how to allocate amounts uniquely. It often requires a bridge table and a specific relationship pattern to handle 'sales territory' or product category mapping. For a clean star schema, you should keep relationships one-to-many from dimension to fact and design proper bridge tables if a genuine many-to-many is needed.

Merge the OrderDate and ShipDate columns into a single column and use a flag to differentiate.

Why it fails: Incorrect. Merging dates and using a flag would lose the distinct date context and complicate time intelligence calculations.

Use a single date table with an active relationship to OrderDate and an inactive relationship to ShipDate.

Why it fails: Incorrect. Using a single date table with one active and one inactive relationship is not recommended because it can cause ambiguity with time intelligence functions and does not allow independent filtering on both date dimensions. The best practice is to use separate date tables or USERELATIONSHIP with distinct tables.

Many-to-one (Sales to Products)

Why it fails: While the underlying cardinality is the same, describing it as many-to-one from Sales to Products reverses the natural filter direction and obscures the model's intent. In Power BI, relationships are always defined from the dimension table (Products) to the fact table (Sales), with the filter arrow pointing from the 'one' side to the 'many' side. If you explicitly set many-to-one with Sales as the 'many' side, you risk creating an ambiguous cross-filter direction and making DAX measures less intuitive. The correct convention is to state the relationship in the order that supports the required filter flow: Products → Sales.

Many-to-many

Why it fails: A many-to-many relationship would imply that a single product can appear in multiple sales and that a single sale can include multiple products. This would necessitate a bridge table, such as SaleDetails, with a composite key to resolve the junction. In the given scenario, each sales transaction pertains to exactly one product, so the underlying cardinality is inherently one-to-many. Choosing many-to-many would introduce unnecessary model complexity, potential filter ambiguity, and degraded query performance, and it does not reflect the business logic.

One-to-one

Why it fails: A one-to-one relationship would require each product to have exactly one corresponding sales record, meaning every sale is unique to a product and no product can be sold more than once. This is unrealistic for a sales fact table, which normally records multiple transactions, time-stamped and repeated, for each product. One-to-one cardinality would also collapse the fact table's granularity to the product level, preventing analysis of sales trends, quantities, or individual order details. Consequently, only a one-to-many relationship matches the actual data pattern and supports proper aggregations.

Use calculated columns instead of measures for aggregations.

Why it fails: Calculated columns are evaluated row-by-row at data refresh time and stored as compressed data in memory, so they inflate model size and never respond to slicer, filter, or row context changes. Measures, in contrast, are calculated on the fly at query time, preserve natural filter context, and are optimized by DAX for aggregation semantics like SUM and AVERAGE. Using calculated columns for aggregations forces static pre-computed values that become stale or incorrect as filters change, which is why measures are always the correct choice for dynamic summary calculations.

Combine all data into a single flat table to simplify the model.

Why it fails: Consolidating every field into a single flat table massively denormalizes data, producing wide rows with repeated values that defeat VertiPaq's columnar compression and cardinality optimization. This bloats storage, slows refresh, and makes key-attribute relationships implicit, forcing DAX to rely on filter propagation across unrelated columns—which often yields ambiguous or incorrect results. Flat tables also create data maintenance nightmares because one row change can ripple across multiple business entities, making star schema decomposition the only scalable approach.

Enable bidirectional cross-filtering between all tables.

Why it fails: Enabling bidirectional cross-filtering on every relationship propagates filter context in both directions across all tables, which can create ambiguous join paths when a fact table links to multiple dimensions or when filter paths loop back, leading to unpredictable query results and hidden performance degradation. The Query Optimizer must evaluate multiple possible filter flows, which increases query plan complexity and execution time, and it also multiplies the risk of unwanted filter leakage between unrelated business measures. Bidirectional filtering should be reserved for specific, well-understood many-to-one patterns—not applied globally as a blanket setting.

The TotalDue column is not a numeric type.

Why it fails: The TotalDue column is numeric in the data model—specifically, Power Query defines it as a nullable number. Even if it were stored as text, DAX would implicitly convert it for aggregation in measures such as SUM or AVERAGE. The real problem is not the data type but the fact that the underlying transaction rows have been grouped away, so any numeric type is irrelevant to why filters cannot re-aggregate.

The dataset is not refreshing properly.

Why it fails: Refresh timing does not affect how filters interact with pre-aggregated data. A refresh simply reloads or updates the existing query output; it would still produce the same pre-summarized table with lost detail rows. Therefore, even with a perfectly successful refresh, report-level filters cannot re-aggregate because the underlying granular data remains unavailable. The filtering issue is a query-design problem, not a data-freshness or data-loading problem.

The Date table has no relationship to the Sales table.

Why it fails: This option is also incorrect. Even if a Date table were related to the Sales table, the relationship would be to the summarized Sales table—not to the original OrderDate field that was dropped during the Power Query grouping step. For example, if the Date table links to a Month column that exists in the aggregation, filtering by a specific day would still fail because day-level granularity is absent. Thus, the absence of a relationship is not the root cause; the root cause is the loss of detail rows during pre-aggregation in Power Query.

Convert the model to Import mode.

Why it fails: Switching the entire model to Import storage mode would eliminate query-time evaluation of calculated columns because all data, including computed columns, would be cached in memory. However, this fundamentally changes the data delivery architecture: it requires scheduled refreshes, introduces freshness latency, and may be impossible when the underlying dataset exceeds memory limits or when the business requires real-time visibility into the source system. The question specifically asks for a design change that preserves DirectQuery functionality, so this is not an acceptable trade-off.

Use a measure instead of a calculated column.

Why it fails: Replacing a calculated column with a measure is semantically incorrect because measures are evaluated in filter context, not row context, and they cannot be used as a column in a table visual, a slicer, or a relationship key. A calculated column that augments each row with a related value is typically used for grouping, filtering, or as a lookup column, and a measure simply cannot replicate that materialized per-row value. Moreover, in DirectQuery, measures are still evaluated at query time and may trigger multiple backend requests, so this does not inherently improve performance; it only changes the nature of the calculation.

Replace the calculated column with a calculated table that includes the related column.

Why it fails: A calculated table is an imported, materialized table that is evaluated at refresh time, even when all other tables in the model use DirectQuery, so it computes the related column only once and stores the result in memory. This means that at report query time, the join and the RELATED lookup are already resolved, eliminating the per-query overhead that causes the poor performance. Because the calculated table can be linked via relationships to other tables, it preserves the model's logical structure and functionality without requiring changes to the source query or a full conversion to Import mode. This is why it is the correct design change in this scenario.

Running Total = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Date), Date[Date] <= EARLIER(Date[Date])))

Why it fails: EARLIER is used in calculated columns, not measures. In a measure, there is no row context to reference.

Running Total = TOTALYTD(SUM(Sales[SalesAmount]), Date[Date])

Why it fails: TOTALYTD is a function that calculates year-to-date, but it is used incorrectly; the correct syntax is TOTALYTD(expression, date, filter). However, this also works but is not the simplest. The issue is that it does not handle multiple years; it only shows YTD for each year.

Running Total = SUMX(Sales, Sales[SalesAmount])

Why it fails: This simply sums all sales; it does not restrict to dates up to current context. It will return total sales regardless of filter.

Use calculated columns to perform all transformations.

Why it fails: Calculated columns are written in DAX and are materialized in the model during refresh, adding to the compressed column store and refresh time; they are evaluated row-by-row in the in-memory engine, not during query time. Relying on calculated columns for transformations pushes work into the tabular engine and bloats the model, whereas Power Query (M) can leverage query folding to push transformations back to the source database, improving performance. Therefore, as a best practice, use Power Query for transformations and reserve calculated columns for modeling logic that cannot be done during load.

Set all relationships to bidirectional cross-filtering to avoid ambiguity.

Why it fails: Enabling bidirectional cross-filtering on every relationship makes filters flow in both directions unconditionally, which can create ambiguous filter contexts and cause unexpected row counts in dashboards. It also expands the set of tables that must be evaluated for each query, increasing calculation engine overhead and often leading to performance degradation. The recommended pattern is to use single-direction filter propagation by default and enable bidirectional only for a specific many-to-many relationship or slicer formula where the extra filter direction is strictly required.

Reference other queries as a single source to avoid duplication.

Why it fails: Referencing another query re-evaluates the source query for each reference branch, and if the referenced query is used multiple times, the same data is held in memory and reprocessed repeatedly, which can significantly slow down refresh. The best practice is to avoid creating a long chain of references solely to avoid typing the same M code; instead, load a single clean source table in Power Query and build the dimensional model after loading. Priorities that reduce duplication include the query editor's native merge/append operations and a well-designed staging query, not recursive query references.

Use a single date dimension table and keep only one active relationship (e.g., to OrderDate). For other dates, create measures that filter the date table using CALCULATE and FILTER.

Why it fails: While this approach uses a single date table, it relies on manual filtering with CALCULATE and FILTER, which is inefficient and not standard. The best practice is to use inactive relationships with USERELATIONSHIP.

Create a separate date dimension table for each date column in the Sales table and relate each to the fact table.

Why it fails: Creating separate date dimension tables for each date column leads to unnecessary duplication and complexity, making the model harder to maintain.

Use a single date dimension table and create multiple active relationships using bridging tables or by duplicating the dimension table for each role.

Why it fails: Creating multiple active relationships through bridging tables or duplication is not recommended because it complicates the model and can lead to ambiguity in filtering.

SUMX cannot iterate over Inventory directly because it is a table reference; you need to use VALUES or a measure.

Why it fails: SUMX accepts any table expression, and a base table reference like Inventory is a valid table expression in DAX. There is no requirement to wrap it in VALUES or a dedicated measure; SUMX will iterate over every row in Inventory. The actual problem is not the iteration itself but that the CALCULATE filter is applied to the entire Inventory table without respecting the row context that SUMX creates.

The measure needs to be written using SUM instead of SUMX because StockOnHand and UnitPrice are both numeric columns.

Why it fails: SUM cannot perform the row-by-row multiplication needed here because it only aggregates a single numeric column. In this scenario, the product of StockOnHand and UnitPrice must be computed per inventory row and then summed, which is exactly the purpose of SUMX. SUM would either require a precomputed column or would return an incorrect result by summing one of the columns alone.

The RELATED function fails because the relationship between Inventory and Product is inactive.

Why it fails: RELATED relies on an active relationship between Inventory and Product, and in the default model that relationship is active, so RELATED would return the correct value. Even if it were inactive, the failure described in the question is not related to relational lookup; it stems from the CALCULATE filter not being evaluated in the SUMX row context. The solution involves using FILTER inside CALCULATE to make the condition row-aware, not changing relationship activation.

The Discount column might have negative values causing the total to be incorrect.

Why it fails: Negative values in the Discount column would only affect the calculated total by lowering it, but they have no bearing on whether a date slicer filters the report. Filter behavior is determined by the table relationships and the active filter context, not by the sign of the numbers in a column. If negative discounts are present, that is a data validation concern, not a DAX context issue. Therefore, this explanation does not address the reported symptom.

The measure should use SUM instead of SUMX because there are no row-level calculations.

Why it fails: The measure likely multiplies a quantity or list price by the discount rate at the row level, which requires SUMX to iterate over each row and then sum the results. Using SUM would simply add all values in the Discount column, ignoring any row-level multiplication and producing an incorrect total. Even if the current formula appears simple, the presence of a discount rate usually implies a row context calculation, so switching to SUM is not a valid fix. Thus, this option misdiagnoses the need for SUMX.

The measure does not use the Date table; it references only the Sales table, so it ignores filters from other tables.

Why it fails: A measure that references only the Sales table does not ignore filters from the Date table if a valid, active one-to-many relationship exists from Date to Sales. Filter context from a slicer on the Date table automatically propagates to Sales through that relationship, so the measure will be evaluated within the selected date range. The absence of a direct DAX reference to the Date table is irrelevant; relationship filtering handles it. Therefore, this explanation misunderstands how filter propagation works in Power BI.

Remove the relationship for DestinationCity and use a DAX measure with LOOKUPVALUE to get the destination state.

Why it fails: Removing the destination relationship and using a LOOKUPVALUE measure is inefficient and breaks proper filtering. LOOKUPVALUE is a row-context lookup that must be evaluated for each shipment row and cannot participate in relationship-based filter propagation from a slicer; you would need to embed the lookup in a FILTER expression, losing query optimization and causing performance degradation. Additionally, this design makes it impossible to slice by destination state using standard visual interactions, and it simply abandons the relational model Power BI is built on.

Create a new table 'Route' that contains all unique combinations of origin and destination cities, and use that as a filter table.

Why it fails: A Route table of every origin–destination pair is not a bridge table because it has no direct relationship to Shipments and does not resolve the ambiguity of filtering by a single city. If you try to filter by a city, the Route table would need to match either the origin or destination column, which requires a complex OR condition that is not supported by standard relationships; it would actually create a many-to-many fan trap that multiplies shipment counts. The standard solution is to keep separate role-playing dimensions and control the active relationship with DAX, not to pre-join combinations.

Keep both relationships active and set the cross-filter direction to both.

Why it fails: Power BI does not permit two active relationships between two tables; when you attempt to set both as active, the model will show an error and force you to deactivate one. Even if it were allowed, setting both cross-filter directions to 'Both' would make the relationship direction ambiguous and risk circular or contradictory filter contexts. You must keep exactly one relationship active and use USERELATIONSHIP inside CALCULATE to switch to the inactive relationship when needed.

Add calculated columns for year, month, and quarter to the Sales table.

Why it fails: Adding calculated columns for year, month, and quarter directly to the Sales table is a modeling anti-pattern because these columns are physically materialized and stored for every row, expanding the table's memory footprint and slowing refresh. More importantly, they prevent the use of a single authoritative date dimension, leading to duplicate logic and inconsistent date rollups across reports. DAX time intelligence requires a contiguous date table; calculated columns cannot substitute for that, and any date slicing is confined to Sales' own data.

Create relationships between tables using the foreign key columns.

Why it fails: Creating relationships 'using the foreign key columns' is wrong because relationships in Power BI must join the foreign key in the fact table to the primary key (or a unique column) in the dimension table. If you connect based on a foreign key that contains duplicates, or if you treat foreign keys themselves as the one side, the model will create an incorrect cross-filtering context and can even yield many-to-many behavior. The correct design maps each fact foreign key to the dimension's unique primary key to guarantee referential integrity.

Combine all data into a single flat table to simplify the model.

Why it fails: Combining all data into a single flat table destroys the star schema and introduces massive data redundancy, because dimension attributes like product name or store location are repeated on every sale row. This inflates memory usage, slows down query and refresh times, and makes it impossible to apply row-level security or to maintain business logic in a single place. Flat tables also force you to mix grain levels (e.g., sales transactions vs. product details) in one table, which violates normalization and makes DAX measures more error-prone.

Test the roles using the 'View as' feature, then create roles and define DAX filters in Power BI Desktop, then publish the report to the Power BI service, then assign users or groups to the roles in the service.

Why it fails: Starting with 'View as' is logically impossible because that feature requires at least one role to exist; with no roles defined, there is nothing to impersonate. Even if roles were carried over from a previous edit, changing the DAX filters after testing would invalidate the results you just checked. Role creation must always precede testing, just as validation must precede publication.

Create roles and define DAX filters in Power BI Desktop, then publish the report to the Power BI service, then test the roles using the 'View as' feature, then assign users or groups to the roles in the service.

Why it fails: This order interposes publishing before local testing, but the 'View as' feature is only available in Power BI Desktop's Modeling tab — it is not exposed in the Power BI service. Deploying an untested model to a shared workspace risks exposing filter mistakes to real users before you have a chance to impersonate a role and discover the error. Correct validation happens before publish; the service is used solely for assigning members to the roles that were already defined and tested.

Publish the report to the Power BI service, then create roles and define DAX filters in Power BI Desktop, then test the roles using the 'View as' feature, then assign users or groups to the roles in the service.

Why it fails: Publishing first cannot work because RLS definitions are stored in the report's data model, and the Power BI service has no user interface for creating roles or writing DAX filter expressions. A dataset published without roles is entirely un-restricted, and any later role creation in Desktop would require a full re-publish of the report. The 'View as' test must be run in Desktop before publishing, so this sequence skips the only step that lets you check filter results before they go live.

1. Mark the table as a date table. 2. Use CALENDAR to generate a date range. 3. Add calculated columns.

Why it fails: This sequence is invalid because you cannot assign the "Mark as Date Table" property to a table that has not yet been created. The CALENDAR function must be evaluated first to materialize the date table, and then you need to add supporting columns like Year and Month to make the table useful for time intelligence. Without an existing table, Power BI has no object to mark, and doing so would cause an error. Even if a table existed, marking it before adding calculated columns is risky because the date table's structure is still incomplete for hierarchical analysis.

1. Add calculated columns. 2. Use CALENDAR to generate a date range. 3. Mark the table as a date table.

Why it fails: Adding calculated columns before generating the date range is logically impossible, because calculated columns must reside in an existing table, and the CALENDAR function is what creates the base date table. There is no target table for the computed columns until after CALENDAR returns a single column of dates. Attempting to add calculated columns first would leave DAX with no table context, and you would have to revisit the definitions after the table exists, making the sequence inefficient and error-prone. The correct flow is to first create the date range with CALENDAR, then add the derived columns.

1. Use CALENDAR to generate a date range. 2. Mark the table as a date table. 3. Add calculated columns.

Why it fails: Marking the table as a date table immediately after generating the date range is premature because at that point the table contains only dates, with no Year, Month, Quarter, or other attributes needed for meaningful time-intelligence calculations and drill-downs. While Power BI requires the date column to be unique and continuous, the marking action does not automatically create or populate those descriptive columns, and time-intelligence measures like TOTALYTD won't leverage them if they don't exist. Furthermore, adding calculated columns after marking can invalidate the date table designation because the table's structure undergoes schema changes, forcing you to re-verify the marking criteria.

Click Quick Measure in the Modeling tab, select the table for the measure, choose the calculation type, specify the fields, and then click OK.

Why it fails: This order fails because the Quick Measure button is disabled or misdirected until you select a target table in the Fields pane. In Power BI Desktop, the new measure is always created in the currently active table — the one you have selected before clicking Quick Measure in the Modeling tab. If you click the button first, the measure may land in whatever table happens to be selected, often the first one or the last used, rather than your intended table. Correctly selecting the table first ensures the measure is stored in the right location for easy reference in reports.

Select the table for the measure, click Quick Measure in the Modeling tab, specify the fields, choose the calculation type, and then click OK.

Why it fails: This sequence is invalid because the calculation type must be selected before field mapping becomes meaningful. In the Quick Measure dialog, the Calculation dropdown defines the template for the measure, which in turn determines the exact field inputs that appear in the field selection area. If you try to specify fields first, the dialog has no context for what those fields represent — e.g., 'Base value' and 'Category' are only defined after choosing 'Average per category.' Therefore, specifying fields before choosing the calculation type is impossible or at best produces a mismatched or non-functional measure once you eventually pick the calculation.

Select the table for the measure, click Quick Measure in the Modeling tab, choose the calculation type, click OK, and then specify the fields.

Why it fails: This order is incorrect because clicking OK while field inputs are still unspecified either triggers a validation error or generates a placeholder measure that lacks the required arguments. The Quick Measure dialog does not allow you to finish creation without populating all mandatory field slots; pressing OK prematurely closes the dialog and discards your selected calculation type. After the dialog closes, you cannot append fields to that quick measure — you would have to reopen the dialog from scratch or manually rewrite the DAX. Thus, fields must be defined before the OK click, not after.

Power BI Mobile: Used to create and edit reports.

Why it fails: The mobile apps (iOS, Android, Windows) are designed for consumption and on-the-go access to dashboards and reports, with features like touch-enabled interaction, and they support annotating and sharing snapshots. They do not provide the full authoring capabilities of Power BI Desktop, such as creating data models or complex DAX measures, so the premise is incorrect.

Power BI Gateway: Used to connect cloud data sources to Power BI.

Why it fails: The on-premises data gateway acts as a secure bridge between the Power BI Service and on-premises data sources, enabling refreshes of datasets that use local data, without moving the data to the cloud. It does not connect cloud data sources; rather, it is used for hybrid scenarios, and cloud data sources like Azure SQL Database can be accessed directly without a gateway.

Dashboards: A bundled collection of dashboards and reports for distribution.

Why it fails: This pairing is incorrect because a bundled collection of dashboards and reports for distribution is called an app, not a dashboard. A dashboard is a single canvas that can display many tiles, but it does not itself bundle other dashboards or reports; it aggregates visual objects. The word 'bundle' refers to packaging multiple artifacts for an audience, which is an app's purpose.

Workspaces: A single-page canvas for visualizing and interacting with data.

Why it fails: This description wrongly assigns a dashboard property to workspaces. Workspaces are not visual interfaces; they are purely organizational containers in which artifacts are stored and refined. You view and interact with a single-page canvas inside a dashboard, whereas a workspace simply holds the underlying items and controls who can edit or view them.

Apps: Containers for collaborating on Power BI content.

Why it fails: This is incorrect because apps are not environments for collaboration; they are final artifacts for consumption. Collaboration happens earlier in the lifecycle, inside a workspace, where multiple authors share permissions and iteratively build content. An app is a static snapshot, effectively a published bundle that viewers consume, not a container where creators work together.

Row-level security (RLS): Restricts access to specific tables or columns.

Why it fails: This statement incorrectly attributes the behavior of Object-level security (OLS) to Row-level security (RLS). While RLS restricts which rows of data a user can view based on DAX filters applied to roles, it does not hide entire tables or columns; those objects remain visible in the schema, just with fewer rows returned. Hiding or restricting access to specific tables or columns is exclusively the function of OLS, which operates at the metadata level and requires capacity-specific permissions, not row filters.

Object-level security (OLS): Filters data at the row level based on user roles.

Why it fails: This statement incorrectly assigns Row-level security's filtering behavior to Object-level security (OLS). OLS does not evaluate row data or apply dynamic filters; instead, it works on the structural objects of the data model, hiding entire tables or columns from certain roles. The act of filtering rows by user roles is exclusively performed by RLS, which uses DAX expressions to determine row visibility, while OLS ignores individual row values and only controls whether an object is accessible at all.