Creating separate, well-structured date tables (like fiscal and calendar) is a best practice. Using standard time intelligence functions like SAMEPERIODLASTYEAR is both performant (as they work with the date table relationship) and maintainable. Pre-computing columns in the fact table (Option B) bloats the model and isn’t dynamic. Quick measures (Option C) are for convenience, not a scalable design strategy. A single table with multiple roles (Option D) can work but is often less clear than dedicated tables for distinct date contexts.

For DirectQuery, creating aggregate tables is a primary optimization technique. It allows common, high-level queries to hit a small, pre-aggregated table instead of scanning massive detail tables, drastically improving performance. While Performance Analyzer (Option A) is a good diagnostic tool, the root cause is the query structure, not necessarily the DAX. Switching to Import (Option B) may not be feasible due to data latency or size constraints. Adding indexes (Option D) is a source-side action that requires DBA involvement and may not address the fundamental issue of query complexity against large tables.

Dual storage mode is designed for this scenario. Setting the large inventory table to Dual allows the VertiPaq engine to cache its data in-memory when possible (especially for queries that also involve imported data), while still allowing DirectQuery for filters that require the latest data. This often provides the best balance. Forcing a single mode (Option A) removes the benefits of a composite model. Referential integrity (Option B) is important but not the primary performance lever here. You cannot create a calculated table that directly materializes a cross-database join (Option C) in this way.

The most robust pattern is to have a separate table (often called a role or bridge table) that maps user identities (like USERNAME() or USERPRINCIPALNAME()) to the business attributes (RegionID). You then create a relationship from this bridge table to the dimension (Salesperson). An RLS rule on the bridge table (e.g., TRUE()) automatically filters the related Salesperson and, through that, the Sales fact table. This is clean, auditable, and easy to update. LOOKUPVALUE (Option A) can be fragile and less performant. Filtering directly on the fact table (Options C & D) often breaks down with complex models and is harder to maintain.

Tools like Tabular Editor (or the newer Model view in Power BI Desktop) provide a dependency diagram that visually shows relationships and formula dependencies between tables and columns. This is the most direct way to spot a circular reference, where ColumnA depends on ColumnB, and ColumnB (directly or indirectly) depends back on ColumnA. Deleting columns (Option B) is destructive and unnecessary. VertiPaq Analyzer (Option C) is for memory usage, not dependency analysis. While checking same-table references (Option D) is part of it, a circular dependency can span multiple tables, making visual inspection far more efficient.

This scenario requires a dynamic lookup. The Targets table is likely related by Month. The correct DAX uses CALCULATE to modify the filter context for the Targets table, bringing in the target for the current month context (from the ‘Date’ table) using TREATAS or a similar function to create a virtual relationship. Simple subtraction (Option A) fails because the tables aren’t directly summed in the same context. A calculated column (Option C) is static and won’t work for a rolling comparison. The built-in KPI visual (Option D) needs a base measure and a target measure; the target measure itself would need the logic in Option B to work correctly.

The key is to use the ALLSELECTED function. A measure defined as Total Category Sales = CALCULATE([Total Sales], ALLSELECTED(Product[ProductName])) will ignore the Top N filter applied to the visual but still respect the higher-level filter (the selected product category). This allows you to show the correct, unfiltered category total next to a visual showing only the top 10 products. Visual-level filters with “Show items with no data” (Option A) won’t solve the total calculation issue. Separate pages (Option C) break the interactive context. A pre-calculated table (Option D) is inflexible and doesn’t allow dynamic category selection.

For large models in Premium, incremental refresh is critical to manage refresh time and resource consumption by only loading new/changed data. Using the XMLA endpoint allows for advanced management, including scheduling background cache updates (warm-up) to prep the cache before business hours, ensuring consistent performance for users. Full refreshes (Option B) are not scalable for large models. Automatic aggregations (Option C) help with performance but don’t address refresh management holistically. A live connection (Option D) refers to connecting to a published dataset, not the deployment of the primary model itself.

The most effective optimization is to move rarely filtered, high-cardinality text columns out of the data model’s core tables and into display-only locations like tooltips. Hiding the column from client tools prevents it from being queried inadvertently, reducing the data footprint in the VertiPaq engine and speeding up queries. Changing to Decimal (Option A) is invalid and would break data. Hierarchies (Option B) are for navigation, not compression. Sorting (Option D) has minimal impact on a text column used only in visuals.

Creating separate, well-structured date tables (like fiscal and calendar) is a best practice. Using standard time intelligence functions like SAMEPERIODLASTYEAR is both performant (as they work with the date table relationship) and maintainable. Pre-computing columns in the fact table (Option B) bloats the model and isn’t dynamic. Quick measures (Option C) are for convenience, not a scalable design strategy. A single table with multiple roles (Option D) can work but is often less clear than dedicated tables for distinct date contexts.0