Flat tables, star schemas, snowflakes, cardinality, filter direction, and Power Query joins, in plain language.
My first Power BI report was a 12 column table. It worked until finance asked for actual vs budget, and I discovered a flat table can’t share a customer or date across two business processes. That’s the lesson this post is really about: the model is the product, not the visuals sitting on top of it.
Modelling shape. A flat table puts everything in one denormalised row. Simple, but redundant and unable to support more than one fact. A star schema puts a narrow fact table (numbers, foreign keys) in the middle, surrounded by dimension tables (descriptive attributes) connected one to many. It’s what VertiPaq is optimised for: fast, readable, easy DAX. A snowflake schema normalises dimensions further (DimProduct > DimSubcategory > DimCategory), trimming redundancy but adding hops and complexity. For almost every project, star wins.
Facts vs dimensions. Ask “did this happen, or does this exist?” Events (SalesAmount, Quantity) go in fact tables like FactSales; entities (CustomerName, Category) go in dimensions like DimCustomer or DimProduct. Every fact table needs a grain. One clear sentence describing what a row means (“one row per order line”). Skip that and you get double counted revenue.
Relationships. A relationship isn’t a merge. It’s metadata declaring a filter path. CustomerID is unique in DimCustomer (one row per customer) but repeats in FactSales (many orders per customer); that relationship is one to many, which should be almost every relationship you create. One to one usually means you forgot to merge two tables in Power Query. Many to many should usually become a bridge table with clean one to many links on both sides. Only one relationship between two tables can be active i.e. role playing dates (order vs ship) use USERELATIONSHIP to activate the second when needed.
Filter direction. Filters flow from the “one” side to the “many” side by default – selecting a category filters sales, not the other way round. Bidirectional filtering looks convenient but creates quite a hefty filter paths across multiple facts, hurts performance, and complicates row level process and security. Prefer CROSSFILTER or TREATAS inside a specific measure over flipping a relationship to both.
Power Query joins. Merge Queries support six join kinds: Left/Right/Full Outer (keep all of one or both sides), Inner (matches only, silently drops the rest), and Left/Right Anti (only the unmatched rows. Great for finding orphan keys or customers who never ordered).
Merge vs relationship. A merge runs at refresh time and physically adds columns – permanent, and costly in memory if overused on facts. A relationship is evaluated at query time and costs almost nothing. Merge dimensions into cleaner dimensions; never merge a dimension straight into a fact table just to avoid building a relationship or you’ll rebuild your flat table problem one merge at a time. Learnt that the hard way.
My default model: star schema, one to many relationships everywhere, a proper DimDate, single direction filtering as the rule rather than the exception, and a written reason required for any bidirectional relationship. It costs a couple of hours up front and saves weeks later, mine cost me a rebuild instead.
Let me know what other hacks you learnt.