Formulas & shortcuts
Power BI · 14
Every formula in this course and the easiest ways to remember them, on one page.
1Getting started and Power QueryBeginner
What is Power BI: BI, components and workflow
Open lessonPower Query: connecting to and cleaning data
Open lessonPower Query: combining queries and M language basics
Open lesson2Data modelling and DAXIntermediate
Data modelling: star schema, relationships and the date table
Open lessonDAX: measures, calculated columns and context
Open lessonDAX: CALCULATE, ALL, FILTER and time intelligence
Open lessonYoY % = (S − SLY) / SLYYoY % = (S − SLY) / SLY
- Ssales in the current period, ₼
- SLYsales in the same period last year, ₼
Year-over-year growth. The denominator is last year's sales, not this year's.
3Reports, sharing and governanceAdvanced
Visualisation: choosing charts, interactivity and dashboard design
Open lessonPublishing, sharing and governance: workspaces, refresh, RLS
Open lesson4University level: advanced DAX and a capstoneUniversity
Advanced DAX and performance: iterators, RANKX, context transition
Open lessonSUMX(T, e) = ∑ e(r), r ∈ T
- Tthe table visible in the current filter context (the rows iterated)
- rone row of T
- e(r)the expression evaluated in the context of row r, e.g. Quantity · UnitPrice
AVERAGEX(T, e) = SUMX(T, e) / |T|, where |T| is the number of rows.
Ttotal = TFE + TSE
- Ttotaltotal query duration, ms
- TFEFormula Engine time, ms (DAX Studio computes it as Ttotal − TSE)
- TSEduration of the Storage Engine queries, ms
Example: if 7200 ms of 9000 ms is FE (80%), rewrite the formula; if SE dominates, shrink the model (columns, cardinality).
Capstone project: a sales analytics dashboard and Copilot
Open lessonAOV = S / NAOV = S / N
- AOVaverage order value, ₼
- Ssales in the selected period, ₼
- Nnumber of orders
On the sample data: 665 / 8 = 83.125 ₼; in Baku 345 / 4 = 86.25 ₼.