Menu
CoddyTech

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.

11 questions
  1. 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 SUM where a distinct count was meant, a time zone shift on dates).

  2. 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 ALL or REMOVEFILTERS on that column. Check the model view first, then the measure.

  3. 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.

  4. 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.

  5. 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.

  6. 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
            )
        )
    

    CALCULATE turns each customer into a filter and removes the date filter, so MIN finds that customer's first order of all time.

  7. 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.

  8. 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 with PATHCONTAINS:

    -- 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.

  9. 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.

  10. 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.

  11. 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] ) ) )
    )
    

    LASTNONBLANK finds the last date in the month that has a snapshot; LASTDATE would return the calendar's last day and give blank when no snapshot was taken that day.

Coddy programming languages illustration

Learn to code with Coddy

Get started