Model the data

You maintain a Power BI model in DirectQuery mode over an Azure SQL Database. The model has a single many-to-one relationship between a large Sales fact table and a Product dimension. You have verified that every ProductKey in Sales exists in the Product table (the source enforces a foreign-key constraint). Query performance on visuals that combine Sales and Product is slower than expected. Which relationship setting should you enable to improve DirectQuery performance while keeping results correct?

You have a star schema with a Sales fact table related to Product and Customer dimensions. A new requirement asks that slicing by Product also filters a separate Inventory fact table that shares the Product dimension. A colleague suggests setting the Sales-to-Product relationship cross-filter direction to 'Both' to make this work. What is the most likely consequence of enabling bidirectional cross-filtering on this dimension relationship?

You are building a sales report in Power BI. A [Total Sales] measure already exists and correctly sums the SalesAmount column. On a matrix visual, products are placed on rows and you need a measure named [% of All Products] that shows each product's sales as a percentage of the grand total across ALL products, so the value stays constant regardless of which product row it appears on. Which DAX definition should you use?

You maintain a sales model with a Sales fact table and a Date dimension. A report page is already filtered to show only the year 2023 via a page-level filter. A business user wants a card visual that always displays total sales for the full year 2024, regardless of any year filters applied elsewhere on the page. Which DAX measure will produce the correct result?

You maintain a Power BI model with 12 base measures (Total Sales, Total Cost, Total Profit, etc.). Business users want each measure to be viewable as Current, Year-to-Date (YTD), Prior Year, and Year-over-Year % without you creating 48 separate measures. You want a maintainable solution that applies these time-intelligence variations to any measure a user places on a visual. Which modeling feature should you implement?

A financial reporting model imports a 60-million-row fact table. Refreshes are slow and the .pbix file is very large. Using DAX Studio's VertiPaq Analyzer, you find that a single column, OrderDateTime (stored as date/time with second-level precision), consumes the largest share of model memory. Reports only ever aggregate sales by day, month, and year — never by time of day. Which action reduces model size while preserving all required reporting capability?

You maintain a Power BI model with a Sales fact table and a Customer dimension joined on CustomerKey (one-to-many, single cross-filter). Management wants a measure that returns the number of unique customers who placed at least one order, so the value must respect any slicers applied to product, date, or region. Which DAX measure correctly meets this requirement?

You maintain a Power BI model with a fact table named Sales containing a [Total Sales] measure, and a marked Date table related to Sales on the OrderDate column. Management wants a measure that shows the running total of sales from the first day of the fiscal year (starting July 1) through the current date in the report context. Which DAX expression should you use?

You are optimizing a large sales semantic model in Power BI Desktop. Before finalizing, you want to inspect the actual row count and a sample of data returned by a new intermediate table variable used inside several measures, without adding a visual to a report page. You open the DAX query view. Which DAX construct should you write to return the sample rows of the underlying calculation for inspection?

You maintain an imported Power BI semantic model that has grown to 1.2 GB, causing slow refreshes and high memory use. Before removing anything, you want to identify which specific columns consume the most storage so you can target the largest offenders. Which approach gives you the most precise, column-level size breakdown?

You maintain a sales model with a large Sales fact table (80 million rows) and several dimension tables. A colleague added a new measure that must return the running count of distinct customers using the DISTINCTCOUNT function. Performance is acceptable, but you notice the Sales table stores a full DateTime column for OrderDateTime, and the model size is much larger than expected. Reporting only needs analysis at the day level, and a separate Date dimension already exists marked as a date table. Which single change will most effectively reduce the Sales table's storage footprint?

You are building a semantic model for a retail company. A stakeholder asks for a Profit Margin % measure calculated as total profit divided by total sales. Some product categories have zero sales in certain months, and the report currently shows Infinity or error values in those cells. You must create a measure that returns a blank value instead of an error when the denominator is zero. Which DAX expression should you use?

You are building a Power BI model for a retail company. The Sales fact table contains an OrderDate column spanning January 2021 through the current date, but there is no dedicated date table in the model. You need to create a date dimension using a DAX calculated table that includes every day in the range with no gaps, so time intelligence functions work reliably. Which DAX expression should you use to define the calculated table?

You are finalizing a star-schema semantic model in Power BI Desktop. The Sales fact table contains a ProductKey column and a CustomerKey column that are used only to define relationships to the Product and Customer dimension tables. Report authors have complained that these numeric key columns clutter the Fields pane and are occasionally dragged into visuals by mistake, producing meaningless aggregations. You must improve the model's usability without breaking the relationships. What should you do?

Your model has a Sales fact table with a many-to-one relationship to a Product dimension. Business users report that a matrix showing Product Category rows correctly filters Sales measures, but when they place a Sales-based measure into a slicer context, the Product table does not get filtered by selections made on other visuals that use Sales attributes. The relationship is set to single cross-filter direction (Product filters Sales). Management wants Product visuals to also respond when users interact with certain Sales-derived selections, but they are concerned about introducing filter ambiguity elsewhere in the star schema. What is the most appropriate action?

You maintain a sales report with a matrix visual that shows a [Total Sales] measure broken down by Product Category (rows) and Region (columns). Business users complain that when they collapse the matrix to view only the grand total, they want the [Total Sales] measure to display blank instead of the aggregated value to avoid confusion during drill-through review. You need a measure that returns the sales value only when the visual context is filtered to a specific Product Category, and returns blank at the grand-total level. Which DAX function should you use to detect whether the Product Category column is currently being filtered?

A Power BI model has a Sales fact table and a Product dimension with a Category column. You write a measure to show only sales that exceed the overall average, but when placed in a matrix broken down by Category, the measure incorrectly compares each category to itself rather than to all categories. The current definition is: HighValueSales = IF([Total Sales] > CALCULATE(AVERAGE(Sales[Amount])), [Total Sales]). What is the correct way to compare each category's total against the average across ALL categories?

You are building a star schema in Power BI Desktop. The Sales fact table contains one row per order line and includes a ProductKey column. The Product dimension table contains one row per product with a unique ProductKey. When you create a relationship between the two tables on ProductKey, which cardinality and cross-filter direction should Power BI apply by default and that you should keep for optimal query performance?

You are building a Power BI model with a fact table named Sales and a separate Calendar table containing one row per day covering all sales dates. You create a measure using TOTALYTD, but it returns incorrect year-to-date results and Power BI shows a warning that automatic date/time intelligence may not work reliably. The Calendar table has a proper Date column of type Date and a one-to-many relationship to Sales[OrderDate]. What should you do to ensure the time intelligence functions calculate correctly?

You maintain a Power BI model with a Sales fact table containing one row per order line, and an Orders table with one row per order. You need a measure that returns the average revenue generated per order (total sales divided by the number of distinct orders), and it must respond correctly to filters on product category, region, and date. Which DAX expression should you use?