Your report total does not match the source system. How do you find out why?
Narrow it down step by step. Check the filters first (report, page and visual filters, slicers, a role in View as) and when the data last refreshed. Then compare row counts after each Power Query step: a filter, Remove Errors or Remove Duplicates may drop rows. Check relationships (sales with unknown keys land in "(Blank)"), data types (text numbers) and the measure itself (a
SUMwhere a distinct count was meant, a time zone shift on dates).A measure shows the same number on every row of a table. What is wrong?
The rows come from a table whose filter does not reach the measure's table. Usually the relationship is missing, inactive, or points the wrong way (the axis column is on the many side and the direction is Single). Less often the measure itself removes the filter, with
ALLorREMOVEFILTERSon that column. Check the model view first, then the measure.A slicer does not filter one of the visuals. What do you check?
Edit interactions: the slicer may be set to none for that visual. Then the model: the slicer's table must be related to the visual's data with a filter direction that reaches it. Then the measure: it may ignore the column with
ALL. On another page, the slicer must be synced there. A slicer on a disconnected table, such as a parameter, filters nothing until a measure reads it.Users say a report is slow. Walk me through what you do.
Reproduce it and measure: Performance Analyzer shows which visual and whether the time is in the DAX query or the rendering. Run the slow query in DAX Studio with Server Timings. Check the model with VertiPaq Analyzer (large columns, high cardinality, bidirectional relationships) and the storage mode. Fix the biggest cause first, often one measure or one huge column, measure again, and only then cut visuals or add aggregations.
How do you build budget versus actual when the budget is by category and month but sales are by product and day?
Keep Budget as its own fact table, related to a Category table and to the date table at month level (for example through a first of month date column). Sales relates to Product, which relates to Category. Then hide the budget below its grain:
Budget Amount = IF ( NOT ISINSCOPE ( Product[Product] ), SUM ( Budget[Amount] ) )Variance measures compare
[Total Sales]with[Budget Amount]at category and month level.How do you count new customers in the selected period?
A customer is new when their first purchase ever falls in the period:
New Customers = VAR PeriodStart = MIN ( 'Date'[Date] ) VAR PeriodEnd = MAX ( 'Date'[Date] ) RETURN COUNTROWS ( FILTER ( VALUES ( Sales[Customer Key] ), VAR FirstPurchase = CALCULATE ( MIN ( Sales[Order Date] ), REMOVEFILTERS ( 'Date' ) ) RETURN FirstPurchase >= PeriodStart && FirstPurchase <= PeriodEnd ) )CALCULATEturns each customer into a filter and removes the date filter, soMINfinds that customer's first order of all time.How do you show the top 5 products and group the rest as Others?
A visual can only show values that exist in a column, so first add an axis table with every product name plus one row "Others" (built in Power Query or as a calculated table), not related to the model. The measure then checks the axis value: for a product in the top 5 of the current selection (from
TOPN ( 5, ALLSELECTED ( Product[Product] ), [Total Sales] )) it returns that product's sales; for Others it returns the total minus the top 5; for any other product it returns blank.How do you let managers see their own team and everyone below them?
Store the reporting chain on each employee with
PATH, then filter in the role withPATHCONTAINS:-- calculated column on Employee Manager Path = PATH ( Employee[Employee ID], Employee[Manager ID] ) -- role filter on Employee PATHCONTAINS ( Employee[Manager Path], LOOKUPVALUE ( Employee[Employee ID], Employee[Email], USERPRINCIPALNAME () ) )Each employee's path lists every manager above them, so a manager matches everyone in their branch.
How do you let readers compare two periods they choose themselves?
Add a second date table, Compare Date, with an inactive relationship to the sales date, and a slicer on each date table. The comparison measure ignores the main date filter and uses the other relationship:
Sales Compare Period = CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Date' ), USERELATIONSHIP ( 'Compare Date'[Date], Sales[Order Date] ) )[Total Sales]follows the main slicer, this measure follows the second, and a difference measure subtracts them.A "(Blank)" row appears in a chart by category. Why, and how do you fix it?
Some fact rows have a key that does not exist in the dimension, for example a product added to Sales before it was added to Product, so the engine puts them in a blank row. Do not filter "(Blank)" out of the visual: the total then hides real sales. Find the missing keys, fix them at the source or add an Unknown member to the dimension, and refresh so every key matches.
How do you report an inventory balance by month?
A balance is semi additive: it adds up across products and stores but not across time, so a month shows the balance on its last day, not the sum of daily balances.
Closing Units = CALCULATE ( SUM ( Inventory[Units] ), LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( Inventory[Units] ) ) ) )LASTNONBLANKfinds the last date in the month that has a snapshot;LASTDATEwould return the calendar's last day and give blank when no snapshot was taken that day.
Power BI Scenario Based Interview Questions
Problems you solve out loud: totals that do not match, slicers that do not filter, budget versus actual, new customers and dynamic security.