Why a Weak Power BI Answer Doesn't Always Mean Rejection

Q: Show last year's sales next to this year's.

Candidate:
'I don't remember the exact function name. What I
need is total sales evaluated with the date filter
moved back one year. That only works with a proper
date table, marked and related to Sales.

I can write it with DATEADD, which I remember:'

Sales LY =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, YEAR ) )

Interviewer: There is also SAMEPERIODLASTYEAR.

Candidate: 'Right, that is the shortcut for the
same shift by one year.'

Score: concept fully shown, name supplied.

Q: Each region's share of sales, where selected
   regions add up to 100%.

First attempt:
Region % =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Store[Region] ) ) )

Interviewer: Select two regions. Do they add to 100%?

Candidate:
'No. ALL ignores the slicer too, so the total is
every region. I want the total of what is
selected, so ALLSELECTED.'

Region % =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Store[Region] ) ) )

-- Bonus: 1000 per store that beats its target
Store Bonus =
IF ( [Total Sales] > [Total Target], 1000, 0 )

Table visual by Store:
Store A   1000
Store B      0
Store C   1000
Total     1000   <- expected 2000

Why: at the total row, there is no single store.
The measure compares ALL stores' sales with ALL
stores' targets, gets one TRUE, and returns 1000
once. It never adds the store rows.

Store Bonus =
SUMX (
    VALUES ( Store[Store Name] ),
    IF (
        CALCULATE ( [Total Sales] ) > CALCULATE ( [Total Target] ),
        1000,
        0
    )
)

-- At a store row: VALUES returns one store,
-- result is 1000 or 0 as before.
-- At the total row: SUMX iterates every store,
-- checks each one, and adds the bonuses: 2000.

-- Alternative: show nothing at total level
-- IF ( ISINSCOPE ( Store[Store Name] ), [store logic], BLANK () )

-- A measure that returns an unexpected number
Avg Sales per Customer =
VAR TotalSales = [Total Sales]
VAR CustomerCount = DISTINCTCOUNT ( Sales[CustomerID] )
VAR Result = DIVIDE ( TotalSales, CustomerCount )
RETURN
    Result

-- To debug, return one variable at a time:
--   RETURN TotalSales      (is the numerator right?)
--   RETURN CustomerCount   (is the count right?)
-- Compare each with the source for one filter.

-- Explaining this in an interview shows a calm,
-- step-by-step approach to a wrong number.

GAP: totals as calculated columns

Customer Total (column) =
CALCULATE ( SUM ( Sales[Amount] ), ALLEXCEPT ( Sales, Sales[CustomerID] ) )

Problems:
- Repeated on every sales row for that customer
- Summing it in a visual multiplies the total
- Does not respond to slicers (computed at refresh)

FIX: a measure
Customer Sales = SUM ( Sales[Amount] )

Use a column only when you need a row attribute
you will slice or filter by, such as
Order Size = IF ( Sales[Amount] >= 5000, "Large", "Small" )

'I don't remember the exact function, but here
is what the calculation needs to do:

- Which value: ______ (e.g. total sales)
- Under which filters: ______ (e.g. last year)
- Which filter to remove or shift: ______

I can write it with ______ which I know, and
I would check the docs for the shortcut.'

Then write the version you know.
Never invent a function name.

'That number looks off. Let me check it.'

1. Which filters apply to this cell?
2. Is this a total row? If so, the measure
   runs on the whole selection, not a sum.
3. Split the measure into variables and
   return each one in turn.
4. Compare one value with the source.
5. Check the relationship if a slicer has
   no effect.

Say each step aloud as you do it.

'I haven't used row-level security in a project
yet. What I understand is that roles apply DAX
filters to tables, and the filters flow through
relationships, so a role on Region would limit
sales rows too.

I would test it with View as before publishing.
It is on my list to practise next.'

Structure:
1. Honest admission
2. What you understand
3. How you would check it
4. Willingness to learn