Why Power BI Eliminates More Candidates Than They Expect

FLAT TABLE (what many submit)
Sales_All: OrderID, OrderDate, CustomerID, CustomerName,
  CustomerCity, CustomerSegment, ProductID, ProductName,
  Category, SubCategory, Qty, Price, Discount, Amount ...

STAR SCHEMA (what reviewers hope for)
FactSales:   OrderID, OrderDate, CustomerID, ProductID,
             Qty, Price, Discount, Amount
DimCustomer: CustomerID, Name, City, Segment
DimProduct:  ProductID, Name, Category, SubCategory
DimDate:     Date, Year, Month, Month No, Quarter

Relationships (one-to-many, single direction):
DimCustomer[CustomerID] -> FactSales[CustomerID]
DimProduct[ProductID]   -> FactSales[ProductID]
DimDate[Date]           -> FactSales[OrderDate]

Q: Why did you split the data into several tables?

Weak: 'It looks more professional.'

Strong:
'The fact table holds one row per order line with
only keys and numbers. Customer and product details
live once in their own tables, so the model is
smaller and filters flow in one predictable direction,
from dimensions to the fact.

It also makes measures simpler. Customer count is
just COUNTROWS(DimCustomer) under the current filter,
and slicing by segment works for every measure
without extra logic.'

let
    Source = Excel.Workbook(File.Contents("C:\Data\targets.xlsx"), null, true),
    Targets = Source{[Item="Targets",Kind="Sheet"]}[Data],
    Promoted = Table.PromoteHeaders(Targets, [PromoteAllScalars=true]),
    Unpivoted = Table.UnpivotOtherColumns(Promoted, {"Region"}, "Month", "Target"),
    Typed = Table.TransformColumnTypes(Unpivoted,
        {{"Region", type text}, {"Month", type text}, {"Target", type number}})
in
    Typed

// Before: Region | Jan | Feb | Mar
// After:  Region | Month | Target  (one row per month)

let
    Source = Csv.Document(File.Contents("C:\Data\sales.csv"),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Trimmed = Table.TransformColumns(Promoted,
        {{"City", each Text.Proper(Text.Trim(_)), type text}}),
    Typed = Table.TransformColumnTypes(Trimmed,
        {{"OrderDate", type date}, {"Amount", type number}}, "en-IN"),
    NoErrors = Table.RemoveRowsWithErrors(Typed, {"Amount"}),
    NoBlanks = Table.SelectRows(NoErrors, each [OrderID] <> null and [OrderID] <> "")
in
    NoBlanks

// The "en-IN" culture reads dates as day/month/year.

Modeling -> Manage roles -> New role: North Manager
Table: DimRegion
Filter DAX:
[Region] = "North"

Test: Modeling -> View as -> North Manager
All visuals now show only North data, because
DimRegion filters FactSales through the relationship.

In the Service:
Dataset -> Security -> add users or groups to the
North Manager role.

Limitation: one role per region becomes hard to
manage as regions grow.

Table: UserRegion (Email, Region)
  priya@company.com  | North
  arjun@company.com  | South

Relationship:
DimRegion[Region] -> FactSales[Region] (one-to-many)
UserRegion stays unrelated, used only as a lookup.

Role: Regional Manager
Table: DimRegion
Filter DAX:
[Region]
    IN CALCULATETABLE (
        VALUES ( UserRegion[Region] ),
        UserRegion[Email] = USERPRINCIPALNAME ()
    )

One role for everyone. Each user sees only the
regions mapped to their email. Test with
'View as' -> Other user -> enter an email.

MODEL PRACTICE (one evening)

1. Take a flat sales file.
2. In Power Query, reference it three times:
   - FactSales: keys and numbers only
   - DimProduct: ProductID, Name, Category
     -> Remove Duplicates on ProductID
   - DimCustomer: CustomerID, Name, City, Segment
     -> Remove Duplicates on CustomerID
3. Add a Date table and mark it.
4. Create one-to-many, single-direction relationships.
5. Write 5 explicit measures.
6. Practise a 60-second explanation of the model.

POWER QUERY PRACTICE

[ ] Unpivot a wide month-columns table
[ ] Combine 3 monthly CSVs from a folder
[ ] Merge a product list into sales (left outer)
[ ] Trim and Proper-case a city column
[ ] Set types with the English (India) locale
[ ] Remove error rows and count them first
[ ] Rename every step to say what it does
[ ] Open Advanced Editor and read the M code

Then add a 4th CSV to the folder and refresh.
Did everything update without manual work?

SERVICE PRACTICE (free account if available)

[ ] Publish the report to a workspace
[ ] Set a scheduled refresh (or explain gateway)
[ ] Create a static RLS role and test View as
[ ] Build a UserRegion table for dynamic RLS
[ ] Explain workspace vs app in two sentences
[ ] Find refresh history and read one error

If you cannot access the Service, write the
steps from memory and say them out loud.
Interviewers accept honest, specific answers.