Many beginners open Power BI, drag a few fields onto the canvas, get a chart and think they have learned it. Then a real task arrives: three messy files, a manager asking for "month-on-month change by region", and numbers that do not match finance's report. Power BI is much more than dragging fields. It has three layers, and the order matters.
Layer 1: Power Query (clean the data)
Power Query is the "Transform data" window. It is where you fix the data before it reaches your report, and every step you take is recorded so it re-runs automatically when the data refreshes.
Typical steps:
- Remove empty rows and junk header rows from Excel exports.
- Set the correct data type for each column (dates as dates, amounts as decimal numbers).
- Trim spaces and fix inconsistent text ("Mumbai", "mumbai ", "Bombay").
- Split or merge columns, unpivot month columns into rows.
- Append files of the same shape (January, February, March) into one table.
Rule of thumb: if a fix is about the data, do it in Power Query. If it is about a calculation, do it in DAX.
Layer 2: The data model (connect the tables)
Instead of one giant flat table, Power BI works best with a star schema:
- Fact tables hold events with numbers: claims, orders, payments. They are long.
- Dimension tables describe things: customers, products, dates, branches. They are short and each row is unique.
Facts sit in the middle, dimensions around them, connected by one-to-many relationships (one branch โ many claims). This is the same idea as a SQL join, set up once in the Model view instead of written in every query.
Always add a proper Date table marked as a date table. Time intelligence functions (year-to-date, same period last year) depend on it.
Common modelling mistakes:
- Many-to-many relationships created by accident because a "dimension" has duplicate keys.
- Bi-directional filters turned on everywhere, which makes numbers change in confusing ways.
- Keeping one huge flat table, which makes DAX harder and files larger.
Layer 3: DAX (calculate)
DAX is the formula language. Learn the difference between two things first:
- A calculated column is computed once per row and stored, like an Excel column.
- A measure is computed on the fly for whatever the visual is showing (a region, a month, a filter). Most of your numbers should be measures.
A few measures you will write in almost every report:
Total Claims = COUNTROWS ( Claims )
Settled Amount = SUM ( Claims[SettledAmount] )
Avg Settlement Days =
AVERAGEX ( Claims, DATEDIFF ( Claims[IntimationDate], Claims[SettlementDate], DAY ) )
Settled Within SLA % =
DIVIDE (
CALCULATE ( [Total Claims], Claims[SettlementDays] <= 30 ),
[Total Claims]
)CALCULATE is the most important DAX function: it evaluates an expression under modified filters. Once you understand it, the rest of DAX opens up. Always use DIVIDE instead of /, because it handles division by zero.
Time intelligence
Claims LM = CALCULATE ( [Total Claims], DATEADD ( 'Date'[Date], -1, MONTH ) )
Claims MoM % = DIVIDE ( [Total Claims] - [Claims LM], [Claims LM] )
Claims YTD = TOTALYTD ( [Total Claims], 'Date'[Date] )These only work correctly with the Date table from Layer 2.
Layer 4: The page (communicate)
A dashboard is not a collection of every chart you can make. A good one-page layout:
- Top row: four to six KPI cards with the headline numbers and their change.
- Middle: a trend line over time and a comparison by the most important category (region, product, channel).
- Bottom: a detail table or a breakdown for drilling in.
- Left or top: slicers for date, region and other filters.
Keep colours consistent (one colour per meaning), title every visual with the question it answers, and remove anything the reader does not need.
Practise on a real brief
Power BI is learned by building. Our motor claims performance dashboard project gives you raw claim extracts from a fictional insurer, a business requirements document from the head of claims, and asks you to clean in Power Query, model the tables, write the DAX and find what is going wrong in one zone. It covers every layer in this article.
If you also know SQL, many modelling ideas will feel familiar. Our joins guide explains the relationships you set up in the model view.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: