Help with DAX, Power Pivot and Power BI
The server gives your assistant a searchable knowledge base of DAX, Excel's Data Model (Power Pivot) and Power BI Desktop, plus a formula checker. It uses the same guides you can read in Analyze.
What it knows
- DAX fundamentals, CALCULATE and filter context, time intelligence, iterators and relationships.
- A pattern cookbook: percent of total, ranking and Top N, Pareto, new versus returning customers, budget variance, semi-additive balances, calculation groups and field parameters.
- Power Pivot in Excel: loading data, relationships, measures, PivotTables, cube formulas, and what Power BI has that Excel does not.
- Power BI Desktop: Model view, DAX query view, parameters, security and publishing.
- How to model each LearnSQL database, with the real table and column names.
How it uses that knowledge
Four tools do the work. You do not call them yourself; the assistant picks them.
search_dax_knowledge | Finds the right section for a question such as "rolling 12 months" or "measure in Power Pivot" and returns working DAX. |
get_dax_guide | Reads a whole topic or one section when the question needs the full picture. |
list_dax_topics | Lists what is available. |
lint_dax | Checks a formula for common mistakes: unbalanced brackets, SUM(a*b), a measure inside a Boolean CALCULATE filter, FILTER over a whole table, IFERROR, / instead of DIVIDE. |
An example conversation
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])
and explains that SAMEPERIODLASTYEAR shifts the dates in the current filter context back a year.Margin % = SUM(Sales[Price] * Sales[Qty]) / SUM(Sales[Cost])SUM() takes one column, so use SUMX(Sales, Sales[Price] * Sales[Qty]), and that DIVIDE is safer than /.Good questions to ask
- "How do I ... in Power BI, and how do I do the same in Power Pivot?" It knows where the two differ.
- "Why is my total wrong?" Paste the measure and describe the visual; it explains row versus filter context.
- "Which relationship should I create between FactSales and DimDate?" It knows the Contoso keys and the types to cast.
- "Give me a report layout for AR aging." It knows the standard sales, finance and operations layouts.
What it cannot do
- It cannot open your files or see your model. Paste the formula, the table and column names, and what you see.
- It cannot run DAX for you; it checks patterns and explains. Test a formula in Power BI's DAX query view or in DAX Studio.
- Features change between Power BI and Excel versions. If a menu is different on your screen, tell the assistant your version.
Want a finished example to learn from?
Download a complete Power BI project and ask the assistant to explain a measure in it.