Downloads
Reference

Glossary

SQL, DAX, Power Pivot and Power BI terms in plain English.

311 Government
A city's non-emergency request line, app and web form for potholes, trash, noise and similar problems.
4-5-4 calendar Retail
A retail calendar of 52-week years whose quarters are made of 4-, 5- and 4-week periods, so every period has whole weeks.
Accumulating snapshot Modeling
A fact table with one row per process instance (an order) and a date for each stage, filled in as the process moves along.
ADDCOLUMNS DAX
Adds calculated columns to a table expression.
Aggregate SQL
A function that combines many rows into one value: SUM, COUNT, AVG, MIN, MAX.
Aggregation table Modeling
A pre-summarized copy of a large fact table at a coarser grain, used so most queries read thousands of rows instead of millions.
Alias SQL
A temporary name for a column or table in a query, set with AS.
ALL DAX
Removes filters from a table or column. Used in CALCULATE for grand totals and percent of total.
ALLEXCEPT DAX
Removes all filters from a table except those on the columns you list.
Batch mode SQL Server
Processing about 900 rows at a time instead of one, used with columnstore indexes to speed up large aggregates.
BETWEEN SQL
True when a value falls in a range, including both ends: WHERE price BETWEEN 10 AND 20.
Bidirectional filtering Modeling
A relationship whose filters flow both ways. Powerful but can cause ambiguity and slow reports; use sparingly.
Bill of materials Manufacturing
The list of subassemblies and components, with quantities, that go into a product. It can be several levels deep.
Bridge table Modeling
A table that resolves a many-to-many relationship between a fact or dimension and another dimension, such as products and tags.
Budget versus actual Accounting
A report comparing the adopted or amended budget with what was actually spent or earned, by department, fund and account.
CAD Government
Computer-aided dispatch: the system that records an emergency call, the units sent and each time stamp until it clears.
CALCULATE DAX
The DAX function that evaluates an expression in a modified filter context.
Calculated column DAX
A column added to a table by a DAX formula that is evaluated row by row when the model refreshes and stored.
Calculation group DAX
A set of calculation items that change how any measure is calculated, such as YTD or prior year, using SELECTEDMEASURE().
Cardinality Modeling
How rows in two tables relate: one-to-many, many-to-one, one-to-one or many-to-many.
CASE SQL
SQL's if/else: returns a different value depending on which condition is true.
COALESCE SQL
Returns the first value that is not NULL, such as COALESCE(discount, 0).
Column SQL
One attribute stored for every row of a table, such as city or price. Also called a field.
Columnstore SQL Server
A table format that stores each column separately and compresses it, so scans and aggregates over millions of rows are fast and small.
Context transition DAX
What CALCULATE does to a row context: it turns the current row's values into filters.
CTE SQL
Common table expression: a named temporary result defined with WITH and used later in the same query.
Data Model Excel
The in-memory model inside an Excel workbook that holds related tables and DAX measures; Power Pivot manages it.
Data type SQL
The kind of value a column holds: integer, decimal, text, date, and so on.
Database SQL
An organized collection of tables (and views) stored and managed together.
Date table DAX
A table with one row per day and no gaps, marked as the date table so time intelligence works.
DATESYTD DAX
Returns the dates from the start of the year to the current date in context; TOTALYTD is the shorthand.
DAX DAX
Data Analysis Expressions: the formula language of Power BI, Power Pivot and Analysis Services.
DAX query DAX
A statement beginning with EVALUATE that returns a table from a model, used by DAX Studio and the MCP tools.
Degenerate dimension Modeling
An identifier, such as an order number, kept in the fact table because it has no attributes of its own.
Dimension table Modeling
A table of descriptive attributes (product, customer, date) used to slice facts.
DirectQuery Power BI
A mode where Power BI sends queries to the source on every interaction instead of importing the data.
DISTINCT SQL
Returns each different value or combination only once, removing duplicate rows.
DISTINCTCOUNT DAX
Counts the different values in a column, for example unique customers.
DIVIDE DAX
Safe division that returns blank (or a value you choose) instead of an error when dividing by zero.
Enterprise fund Government
A fund for a business-like service, such as a water utility, paid for by its customers rather than by general taxes.
Explicit measure DAX
A measure you write yourself with a name and formula, as opposed to dragging a column and letting the tool sum it.
Fact table Modeling
A table of events or transactions with numeric measures and keys to dimensions (sales, payments).
Factless fact table Modeling
A fact table with keys but no numeric measures, recording that something happened or was possible, such as products on promotion.
FILTER DAX
Returns a table containing only the rows where a condition is true. Often used inside CALCULATE for conditions that compare measures.
Filter context DAX
The set of filters that apply when a measure is evaluated: slicers, rows, columns and filters added by CALCULATE.
Fiscal period Accounting
One month of a fiscal year, numbered 1 to 12 from the first month of that year.
Fiscal year Accounting
An organization's 12-month reporting year, which may not start in January. It is usually named by the year it ends.
Foreign key SQL
A column that refers to the primary key of another table, creating a relationship.
FROM SQL
The part of a query that names the table (or tables) the rows come from.
Fund accounting Nonprofit
Tracking money by fund as well as by account so restricted gifts and grants are used as the donor intended.
Fuzzy matching Power Query
Merging or grouping on values that are similar but not identical, such as company names with typos.
Geocoding Maps
Turning an address into latitude and longitude. The datasets record how each point was found in geocode_quality.
GROUP BY SQL
Splits rows into groups so an aggregate is calculated for each group.
Haversine distance Maps
The great-circle distance between two latitude and longitude points, computed with a short trigonometric formula.
HAVING SQL
Filters groups after aggregation; WHERE filters rows before it.
Implicit measure DAX
An automatic sum or count made by dragging a numeric column into a visual. Prefer explicit measures.
Import mode Power BI
Data is copied into the model and refreshed on a schedule. Fast and works offline.
IN SQL
True when a value is in a list or in the result of a subquery: WHERE country IN ('USA', 'Canada').
Index SQL
A structure that makes lookups on a column faster.
INNER JOIN SQL
A join that keeps only rows with a match in both tables.
Iterator DAX
A function ending in X (SUMX, AVERAGEX) that evaluates an expression for each row of a table.
Join SQL
Combines rows from two tables on matching columns: INNER, LEFT, RIGHT, FULL, CROSS.
KPI Power BI
A key performance indicator: a measure compared with a target and shown with a status.
Latitude and longitude Maps
A location as two decimal degrees: latitude north or south of the equator, longitude east or west of Greenwich.
LEFT JOIN SQL
A join that keeps every row of the left table and shows NULL on the right where nothing matches.
LIKE SQL
Matches text against a pattern: % is any run of characters, _ is exactly one character.
LIMIT and TOP SQL
Return only the first n rows. SQL Server uses TOP n after SELECT; MariaDB and MySQL use LIMIT n at the end.
M language Power Query
The formula language behind Power Query. Every step you click is written as M code you can edit in the Advanced Editor.
MCP AI
Model Context Protocol: a standard that lets an AI assistant use external tools, such as these databases.
Measure DAX
A DAX formula evaluated at query time in the current filter context; stores no data.
Net assets Nonprofit
A nonprofit's equity: what it owns minus what it owes, split into amounts with and without donor restrictions.
Net metering Energy
Crediting a home with solar panels for the electricity it exports to the grid.
Normalization Modeling
Organizing tables to avoid repeating data; transactional systems are usually normalized.
NULL SQL
A missing value. It is not zero or empty text; test it with IS NULL.
ODBC Connect
A standard database driver interface used by Excel and many tools to connect to databases.
ORDER BY SQL
Sorts the result. Ascending by default; add DESC for largest first.
Pavement condition index Government
A 0 to 100 score of road condition from a survey; 100 is new, below about 40 needs reconstruction. PCI for short.
PBIP Power BI
Power BI project format: a folder of text files (model and report) instead of one binary .pbix, good for version control.
Periodic snapshot Modeling
A fact table that records a balance at regular intervals, such as month-end inventory. Balances can be summed across products but not across time.
PivotTable Excel
An Excel summary table you build by dragging fields into rows, columns, values and filters.
Power BI Desktop Power BI
The free Windows app for building Power BI data models and reports.
Power Pivot Excel
The Excel feature and add-in for building a Data Model with relationships and DAX measures.
Power Query Excel / Power BI
The tool (language M) for connecting to data and shaping it before it is loaded.
Primary key SQL
A column (or columns) that uniquely identifies each row of a table.
Proposition 13 Government
California's property tax rule: assessed value is the purchase price, grows at most 2 percent a year, and resets when the property is sold.
Query SQL
A request to the database, usually a SELECT statement, that returns a result set.
Query folding Power Query
When Power Query translates steps into the source's own query so the database does the work.
RANKX DAX
Ranks the current item among a table of items by an expression.
Realization Accounting
The share of work at standard rates that is actually billed and collected, billed amount divided by hours at standard rates.
Relationship Modeling
A link between two tables on a key column that makes filters flow from one to the other.
Row SQL
One record in a table, for example one customer or one order.
Row context DAX
The current row of a table, available in calculated columns and while an iterator loops.
Row-level security Power BI
Roles with DAX filters that limit which rows each user sees.
SAMEPERIODLASTYEAR DAX
Shifts the dates in the current filter context back one year; used with CALCULATE for prior-year comparisons.
Schema SQL
A named container for tables inside a database; it appears in qualified names such as contoso.FactSales.
SELECT SQL
The SQL command that reads data: you list the columns you want and the table they come from. SELECT name, price FROM products;
Semi-additive measure DAX
A measure that adds over most dimensions but not time, such as a balance or inventory level.
Sensor channel IoT
One stream of readings from a device, such as the temperature of a room sensor, sampled at a fixed interval.
Service level agreement Government
A target time to respond to or close a request, such as 48 hours for a pothole. SLA for short.
Slicer Power BI / Excel
A visual or control that filters other visuals.
Slowly changing dimension Modeling
A dimension whose attributes change over time. Type 2 keeps history by adding a row for each version with valid-from and valid-to dates.
Snowflake schema Modeling
A star whose dimensions are split into sub-tables, such as product, subcategory and category. Usually flattened in Power BI.
Soft credit Nonprofit
Giving credit for a gift to someone other than the donor who paid it, such as an employee whose employer matched the gift.
SQL SQL
Structured Query Language: the standard language for reading and changing data in relational databases.
Star schema Modeling
A fact table in the center joined to dimension tables around it. The best shape for DAX.
Stored procedure SQL
A saved, named block of SQL that can be run by name. The public sandbox does not allow running them.
Subquery SQL
A query nested inside another query.
SUMMARIZE DAX
Builds a table of the distinct combinations of columns, optionally with calculated columns; used in DAX queries.
SUMX DAX
Iterator that evaluates an expression for each row of a table and adds the results: SUMX(Sales, Sales[Qty] * Sales[Price]).
Surrogate key Modeling
A meaningless integer key a warehouse assigns to each dimension row, instead of using the business key.
SWITCH DAX
Returns a result depending on the value of an expression, like a tidy set of nested IFs.
T-SQL SQL
The SQL dialect of Microsoft SQL Server (TOP, [brackets]). MariaDB uses LIMIT and backticks.
Table SQL
A grid of data about one kind of thing: each row is a record and each column an attribute.
Time intelligence DAX
Functions and patterns that compare and accumulate over time: year to date, prior year, rolling windows.
Time-of-use rate Energy
An electricity price that depends on the hour: higher at peak times, lower overnight.
TMDL Power BI
Tabular Model Definition Language: a text format for a Power BI semantic model, used by PBIP projects.
TOPN DAX
Returns the top n rows of a table ordered by an expression.
TOTALYTD DAX
Year-to-date total of an expression over a date column: TOTALYTD([Total Sales], 'Date'[Date]).
Transaction SQL
A group of changes that succeed or fail together.
Transient occupancy tax Government
A tax on short stays in hotels, lodges and rentals, usually a percentage of the room charge. Often shortened to TOT or lodging tax.
UNION SQL
Stacks the results of two queries that have the same columns. UNION removes duplicates; UNION ALL keeps them.
Unpivot Power Query
Turns columns into rows, for example twelve month columns into a Month column and a Value column.
USERELATIONSHIP DAX
Makes CALCULATE use an inactive relationship, for example ship date instead of order date.
VALUES DAX
Returns the distinct values of a column that are visible in the current filter context.
VAR and RETURN DAX
VAR names an intermediate value inside a measure and RETURN gives the result. Makes formulas clearer and faster.
View SQL
A saved query that behaves like a table.
Water year Utilities
A year that runs October to September, used by utilities and hydrologists, named by the year it ends.
WHERE SQL
Keeps only the rows that meet a condition, before any grouping happens.
Window function SQL
A function such as ROW_NUMBER or SUM() OVER that calculates across related rows without collapsing them.
WIP Accounting
Work in process: time worked but not yet billed.