Power BI data models, Power Query and relationships

Most Power BI problems that look like DAX problems are really model problems. A clean model with correct relationships makes simple measures work; a messy one makes even SUM return confusing totals. Screenings check that you know where each job belongs.

Power Query: clean before you load

Power Query is where you connect to sources and shape data. Typical steps are removing unused columns, setting data types, splitting columns, filtering out blank rows and merging or appending queries. Each action is recorded as an applied step, so the same cleaning runs again on every refresh.

• Set data types early. A date stored as text breaks time-based visuals.
• Remove columns you will not use. A smaller model is faster.
• Do row-level cleanup here rather than in DAX where possible.

Star schema

Split data into two kinds of tables:

• Fact tables hold events or transactions, such as each sale, with keys and numbers like quantity and amount.
• Dimension tables describe those events, such as Products, Customers, Region and Date.

Dimensions sit around the fact table like the points of a star. Slicers and axes should use dimension columns, and measures should aggregate fact columns.

Relationships and cardinality

Each dimension usually has a one-to-many relationship to the fact table: one product, many sales. The filter flows from the one side to the many side by default, which is single cross-filter direction. Watch for these problems:

• Duplicate keys in a dimension, which turn a one-to-many into many-to-many.
• Mismatched data types between key columns.
• Two active paths between tables. Only one can be active; the other shows as a dotted line and is used with USERELATIONSHIP.

Date table and refresh

Add a continuous date table, mark it as the date table and relate it to the fact table's date column. After publishing to the Power BI service, set up scheduled refresh. Sources on a local network commonly need an on-premises data gateway.

Checklist

Before writing measures, confirm: types are set, keys are unique on the one side, relationships run from dimensions to facts, and a date table is in place.