star schema
Fact tables
Dimension tables
| Characteristic | Dimension table | Fact table |
|---|---|---|
| Model purpose | Stores business entities | Stores events or observations |
| Table structure | Includes a key column and descriptive columns for filtering and grouping | Includes dimension key columns and numeric measure columns that can be summarized |
| Data volume | Typically, contains fewer rows (relative to fact tables) | Can contain numerous rows |
| Query purpose | To filter and group | To summarize |
Analytic queries
An analytic query (DAX) is a query that produces a result from a semantic model. Each Power BI visual, in the background, submits an analytic query to Power BI to query the model.
An analytic query has three phases that are implemented in the following order:
- Filter (RLS + cross filter)
- Group
- Summarize (single result)
Configure report visuals
Fields is a collective term that is used to describe a model resource that can be used to configure a visual. The three different model resources that are fields include:
- Columns
- Hierarchy levels
- Measures
| Model resource | Filter | Group | Summarize |
|---|---|---|---|
| Column | X | X | X |
| Hierarchy level | X | X | |
| Measure | X | X | |
| column can also be set to Do not summarize |
measures can be used to filter data in one special case: to use a measure to filter a visual when the visual displays the measure and the filter is a visual-level filter (so, not a report or page-level filter).
(a measure that used to filter a visual is like the HAVING clause in a SELECT statement.)