DAX Function Cheat Sheet
A handful of DAX functions cover the vast majority of real-world report needs: CALCULATE to modify filter context, SUM/AVERAGE/COUNTROWS for basic aggregation, DIVIDE for safe division that handles divide-by-zero gracefully, SAMEPERIODLASTYEAR and DATESYTD for time intelligence, ALL and ALLSELECTED for removing or partially removing filters, and RELATED to pull a value across a many-to-one relationship. Keeping this short list fluent covers roughly 80% of what shows up in day-to-day report building; deeper functions like RANKX, TREATAS, and windowing functions become relevant only for more specialized analytical patterns.
Cricket analogy: A batter mastering a core set of five reliable shots (cover drive, pull, cut, defense, single-taking) covers most match situations, similar to how a handful of core DAX functions cover most report needs.
-- Core function quick reference
SUM ( Table[Column] ) -- basic aggregation
DIVIDE ( Numerator, Denominator, [AlternateResult] ) -- safe division
CALCULATE ( Expression, Filter1, Filter2, ... ) -- modify filter context
SAMEPERIODLASTYEAR ( 'Date'[Date] ) -- shift to prior year
DATESYTD ( 'Date'[Date] ) -- year-to-date date range
ALL ( Table or Column ) -- remove filters
ALLSELECTED ( Table or Column ) -- remove filters but keep slicer selections
RELATED ( Table[Column] ) -- pull value across many-to-one relationship
RANKX ( Table, Expression, , DESC ) -- rankingKeyboard Shortcuts and UI Navigation
In Power BI Desktop, Ctrl+S saves, Ctrl+Z/Ctrl+Y undo/redo, Ctrl+G groups selected visuals, and Alt-clicking a visual selects the object beneath overlapping elements. The Format pane (paint-roller icon) controls visual-level styling while the Model view (left rail) shows relationships; switching frequently between Report, Data, and Model views is normal workflow, not a sign of disorganization. In the Power BI Service, the search bar at the top can jump directly to any workspace, report, or dataset by name, which is faster than navigating through folder hierarchies once you're managing more than a handful of workspaces.
Cricket analogy: Switching fluidly between Report, Data, and Model views is like a captain switching between batting, bowling, and fielding plans within the same match, not a sign of an unsettled strategy.
Common Error Messages
'Circular dependency detected' typically means a calculated column or measure references itself indirectly through a chain of relationships or other measures — trace the dependency chain in the Model view to find the loop. 'A relationship cannot be created between these two columns because one of the columns must have unique values' means you're trying to make a table the 'one' side of a relationship on a column that isn't actually unique; deduplicate or pick a genuinely unique key. 'DirectQuery connections are limited to a single database' surfaces when you try to combine multiple DirectQuery sources without a Composite model enabled, and 'Query took too long to respond' or timeout errors usually point to an unoptimized source query, missing indexes, or a DirectQuery source struggling under the visual load Power BI is generating.
Cricket analogy: A circular dependency error is like a run-out mix-up where both batters run toward the same end because each was relying on the other's call, an unresolved loop that needs a clear decision to break.
'Query took too long to respond' on a DirectQuery source is rarely fixed by tweaking DAX alone — check for missing indexes on the source database, consider a Composite model with an Import-mode aggregation layer, or evaluate whether the underlying source query itself needs optimization.
Licensing Tiers Quick Facts
Power BI Free lets you build and view reports in Desktop but cannot publish to shared workspaces for collaboration. Power BI Pro, priced per user per month, unlocks publishing, sharing, and collaboration in the Power BI Service and is often bundled into Microsoft 365 E5. Power BI Premium (Per User or Per Capacity) and its successor Microsoft Fabric capacity unlock larger model sizes, paginated reports, dataflows, deployment pipelines, and the ability to share content with viewers who don't hold individual Pro licenses, which matters once an organization needs to distribute dashboards broadly across hundreds or thousands of read-only viewers.
Cricket analogy: Power BI Free is like practicing in the nets alone, Pro is like playing in a club league where you can share results with teammates, and Premium is like a franchise's full stadium broadcast reaching every fan.
With Premium/Fabric capacity, report viewers do NOT need individual Pro licenses to view content shared through an App from a Premium-backed workspace — only the content creators/publishers typically need Pro or a Fabric-capable license, which is the key economic driver for organizations distributing to large read-only audiences.
- Core DAX functions to memorize: SUM, DIVIDE, CALCULATE, SAMEPERIODLASTYEAR, DATESYTD, ALL, ALLSELECTED, RELATED, RANKX.
- Key Desktop shortcuts: Ctrl+S save, Ctrl+Z/Y undo/redo, Ctrl+G group visuals, Alt-click to select overlapping objects.
- 'Circular dependency detected' means a measure or column indirectly references itself through a relationship chain.
- Relationship errors about uniqueness mean the 'one' side of the relationship needs a genuinely unique key.
- DirectQuery timeouts often trace back to source database indexing, not DAX logic itself.
- Power BI Free supports personal use only; Pro unlocks sharing; Premium/Fabric unlocks scale and broad viewer distribution.
- Premium-backed workspaces let viewers consume content without individual Pro licenses, key for large audiences.
Practice what you learned
1. Which DAX function safely handles division that might otherwise produce a divide-by-zero error?
2. What does the 'Circular dependency detected' error typically indicate?
3. What is required to make a table the 'one' side of a one-to-many relationship?
4. Which Power BI licensing tier is required to publish and share reports through the Power BI Service?
5. What is a key benefit of Premium or Fabric capacity for distributing reports to a large audience?
Was this page helpful?
You May Also Like
Power BI Best Practices
A practical checklist of proven techniques for building fast, maintainable, and trustworthy Power BI reports.
Power BI Interview Questions
Common Power BI interview questions across fundamentals, DAX, modeling, and scenario-based problem solving, with strong answers.
Power BI vs Tableau
A grounded comparison of Power BI and Tableau across modeling, calculation languages, licensing, and ecosystem fit.
Building a Sales Dashboard
A step-by-step walkthrough of designing, modeling, and publishing a real-world Power BI sales dashboard.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics