Options include Address, City, Country, Web
Data Category: Tells Power BI what kind of data URL, Image URL
a field holds
Enables map and link behaviour in visuals
Remove unnecessary rows and columns
Sort by Column: Orders one column using
Example: Month name sorted by month number
Examples: GUIDs and full timestamps Avoid high cardinality columns another column
Split Date/Time into separate date and time Reduce Model Size: Removing what the model Sum, Average, Count, Do Not Summarize
Summarization: Default aggregation applied
columns does not need Column Properties
when a field is used
Example: Set Year to Do Not Summarize
Disable Auto Date/Time
Currency, percentage, date format, decimal
Format: Controls how values display
Disable load for staging queries places
Table and Column Properties: Settings that
Prefer Whole Number over Decimal or Text control how fields behave and display Hidden: Removes a field from Report view Hide keys and helper columns
Optimal Data Types: Choosing types that Display Folder: Groups related fields in the
Prefer numeric keys over text keys
compress well Data pane
Relationship columns must share the same type
Location Model view, then Properties pane
Group By in Power Query
Most common and preferred type
Summarise Data: Storing fewer rows at the grain One to Many: One side holds unique values,
Configured with Manage aggregations many side repeats them
reports need Example: Product to Sales
Aggregation Tables: Summary tables answering
Import summary over a DirectQuery detail table
common queries quickly Many to One: Same as one to many, direction
Example: Daily sales summary over transaction reversed
Cardinality Options
rows Optimise Model Performance One to One: Both columns hold unique values Consider merging the two tables
Location: View tab, then Performance analyzer
Avoid where possible
Many to Many: Neither column holds unique
Start recording, then refresh visuals values
Bridge table is the preferred alternative
Performance Analyzer
Splits time into DAX query, Visual display, and
Other Single: Filters flow from the one side to the
Default and preferred setting
many side
Copy the query to analyse it further Performance Tools: Finding slow visuals,
measures, and relationships Cross Filter Direction
Risks ambiguity and slower queries
Relationships: Links between tables that pass
Server Timings show storage and formula Both: Filters flow in both directions
filters along
engine time Use sparingly
DAX Studio: External tool for analysing queries
View Metrics shows column and table sizes Active: The one relationship Power BI uses by
Only one active path between two tables
default
Bidirectional relationships
Relationship Properties Inactive: Dashed line, ignored unless activated Activate with USERELATIONSHIP()
Many to many relationships
Assume Referential Integrity: DirectQuery
High cardinality columns Common Causes of Poor Performance Design the Data Model option that uses inner joins
Nested iterators in measures Autodetect: Power BI creates relationships from
matching column names
Too many visuals on one page
Example Date table used for Order Date and Ship Date
Activate inside CALCULATE() with
Calculated row by row at refresh Role-Playing Dimensions: One dimension used in USERELATIONSHIP()
several roles by a fact table Inactive Relationships: Several relationships,
only one active
Stored in the model and increases size Keeps one copy of the dimension
Calculated Column
Approaches
Uses row context Create with a Reference query
Usable in slicers, rows, and axes Duplicate Tables: Separate copy of the
Each copy has its own active relationship
Calculated Column vs Measure: Frequent exam dimension per role
comparison
Calculated at query time Larger model, simpler measures
Not stored in the model Contiguous dates with no gaps
Measure
Uses filter context Covers full calendar years
Requirements
Responds to slicers and filters Unique values in the date column
Evaluated at refresh and stored in the model Date or Date/Time data type
Calculated Tables: Tables created from a DAX
expression
Example: Date table built with CALENDAR() CALENDAR(): Builds dates between a start and
end date
Created by dragging a numeric field into a
visual Topic 2: Model the CALENDARAUTO(): Builds dates from the range
Creation Options
Power BI applies the default summarization Implicit Data found in the model
Date Tables: Common calendar table that Power Query or the source database
Cannot be reused in other measures supports time intelligence
Implicit vs Explicit Measures: Frequent exam
comparison Right click the table, then Mark as date table
Written by the author using DAX
Mark as Date Table Choose the unique date column
Reusable and fully controllable Explicit
Required for reliable time intelligence functions
Replace implicit measures with explicit ones
Increases model size
SUM(), AVERAGE(), MIN(), MAX() Auto Date/Time: Hidden date tables created for
Basic Aggregators every date column
Disable in Options for large models
COUNT(), COUNTROWS(), DISTINCTCOUNT()
Aggregation Measures: Single value results
from a column Date column in the date table to date column
SUMX(), AVERAGEX(), MAXX(), MINX() Relationship
in the fact table
Iterators: Functions that evaluate an expression
Example: SUMX(Sales, Sales[Quantity] * row by row
Sales[Price])
Static: Fixed DAX filter written into the role Example: [Region] = "West"
Right click a table, then New quick measure Location Role Types
Uses USERPRINCIPALNAME() or USERNAME()
Aggregate per category, Filters, Time Dynamic: Filter based on the signed in user
intelligence, Totals Example: Security table mapping email to region
Quick Measures: Prebuilt DAX templates
Categories
created through a dialog
Mathematical operations, Text Desktop: Modeling tab, then Manage roles
Result is editable DAX Create a role and add a table filter expression
Roles: Rules that restrict which rows a user can
CALCULATE(expression, filter1, filter2) Syntax Row-Level Security see
Setup Test with View as
Example: Sales[Region] = "West" Boolean filter: Simple column condition Service: Assign members under semantic model
Filter Arguments Security
FILTER() evaluates row by row Table filter: Filter returned by a table function
Applies to the Viewer workspace role only
ALL(): Removes filters from a table or columns
Admin, Member, and Contributor bypass the
ALLEXCEPT(): Removes all filters except the rules
listed columns Behaviour
CALCULATE(): Evaluates an expression with a Filters follow relationship direction
REMOVEFILTERS(): Clears filters inside modified filter context
Filter Modifiers
CALCULATE() only Apply security filter in both directions extends
the flow
KEEPFILTERS(): Keeps existing filters instead of
replacing them
USERELATIONSHIP(): Activates an inactive
relationship
Create DAX Calculations
Converts row context into filter context
Context Transition
Occurs when CALCULATE() runs inside a row
context
Current row being evaluated
Row Context
Created by calculated columns and iterators
Row Context vs Filter Context: Frequent exam
comparison
Set of filters applied to the calculation
Filter Context
Created by slicers, visuals, relationships, and
CALCULATE()
Marked date table with a continuous date
Prerequisite
column
TOTALYTD(): Year to date total
TOTALQTD(): Quarter to date total Period to Date
Time Intelligence: Calculations that compare or
TOTALMTD(): Month to date total
accumulate over dates
SAMEPERIODLASTYEAR(): Shifts the dates back
one year
DATEADD(): Shifts dates by a chosen interval
Period Comparison
PARALLELPERIOD(): Returns a full parallel
period
Example: CALCULATE([Total Sales],
SAMEPERIODLASTYEAR('Date'[Date]))
Account balances and inventory levels Typical Cases
Take the last value in the period instead of Semi-Additive Measures: Values that sum
summing across some dimensions but not time
CLOSINGBALANCEMONTH(),
Approach
CLOSINGBALANCEYEAR()
LASTDATE() inside CALCULATE()
Example: Month end stock level
MEDIAN(): Middle value of a column
STDEV.S() and STDEV.P(): Standard deviation
for sample or population
VAR.S() and VAR.P(): Variance for sample or Statistical Functions: Basic calculations on
population spread and ranking
RANKX(): Ranks values within a table
TOPN(): Returns the top rows by an expression
DIVIDE(): Division that handles zero
denominators safely
RELATED(): Pulls a value from the one side of a
relationship
Supporting Functions: Frequently combined
SWITCH(): Evaluates several conditions in with measures
sequence
VAR stores a value, RETURN outputs the result
Variables
Improves readability and performance
Data Category: Tells Power BI what kind of data URL, Image URL
a field holds
Enables map and link behaviour in visuals
Remove unnecessary rows and columns
Sort by Column: Orders one column using
Example: Month name sorted by month number
Examples: GUIDs and full timestamps Avoid high cardinality columns another column
Split Date/Time into separate date and time Reduce Model Size: Removing what the model Sum, Average, Count, Do Not Summarize
Summarization: Default aggregation applied
columns does not need Column Properties
when a field is used
Example: Set Year to Do Not Summarize
Disable Auto Date/Time
Currency, percentage, date format, decimal
Format: Controls how values display
Disable load for staging queries places
Table and Column Properties: Settings that
Prefer Whole Number over Decimal or Text control how fields behave and display Hidden: Removes a field from Report view Hide keys and helper columns
Optimal Data Types: Choosing types that Display Folder: Groups related fields in the
Prefer numeric keys over text keys
compress well Data pane
Relationship columns must share the same type
Location Model view, then Properties pane
Group By in Power Query
Most common and preferred type
Summarise Data: Storing fewer rows at the grain One to Many: One side holds unique values,
Configured with Manage aggregations many side repeats them
reports need Example: Product to Sales
Aggregation Tables: Summary tables answering
Import summary over a DirectQuery detail table
common queries quickly Many to One: Same as one to many, direction
Example: Daily sales summary over transaction reversed
Cardinality Options
rows Optimise Model Performance One to One: Both columns hold unique values Consider merging the two tables
Location: View tab, then Performance analyzer
Avoid where possible
Many to Many: Neither column holds unique
Start recording, then refresh visuals values
Bridge table is the preferred alternative
Performance Analyzer
Splits time into DAX query, Visual display, and
Other Single: Filters flow from the one side to the
Default and preferred setting
many side
Copy the query to analyse it further Performance Tools: Finding slow visuals,
measures, and relationships Cross Filter Direction
Risks ambiguity and slower queries
Relationships: Links between tables that pass
Server Timings show storage and formula Both: Filters flow in both directions
filters along
engine time Use sparingly
DAX Studio: External tool for analysing queries
View Metrics shows column and table sizes Active: The one relationship Power BI uses by
Only one active path between two tables
default
Bidirectional relationships
Relationship Properties Inactive: Dashed line, ignored unless activated Activate with USERELATIONSHIP()
Many to many relationships
Assume Referential Integrity: DirectQuery
High cardinality columns Common Causes of Poor Performance Design the Data Model option that uses inner joins
Nested iterators in measures Autodetect: Power BI creates relationships from
matching column names
Too many visuals on one page
Example Date table used for Order Date and Ship Date
Activate inside CALCULATE() with
Calculated row by row at refresh Role-Playing Dimensions: One dimension used in USERELATIONSHIP()
several roles by a fact table Inactive Relationships: Several relationships,
only one active
Stored in the model and increases size Keeps one copy of the dimension
Calculated Column
Approaches
Uses row context Create with a Reference query
Usable in slicers, rows, and axes Duplicate Tables: Separate copy of the
Each copy has its own active relationship
Calculated Column vs Measure: Frequent exam dimension per role
comparison
Calculated at query time Larger model, simpler measures
Not stored in the model Contiguous dates with no gaps
Measure
Uses filter context Covers full calendar years
Requirements
Responds to slicers and filters Unique values in the date column
Evaluated at refresh and stored in the model Date or Date/Time data type
Calculated Tables: Tables created from a DAX
expression
Example: Date table built with CALENDAR() CALENDAR(): Builds dates between a start and
end date
Created by dragging a numeric field into a
visual Topic 2: Model the CALENDARAUTO(): Builds dates from the range
Creation Options
Power BI applies the default summarization Implicit Data found in the model
Date Tables: Common calendar table that Power Query or the source database
Cannot be reused in other measures supports time intelligence
Implicit vs Explicit Measures: Frequent exam
comparison Right click the table, then Mark as date table
Written by the author using DAX
Mark as Date Table Choose the unique date column
Reusable and fully controllable Explicit
Required for reliable time intelligence functions
Replace implicit measures with explicit ones
Increases model size
SUM(), AVERAGE(), MIN(), MAX() Auto Date/Time: Hidden date tables created for
Basic Aggregators every date column
Disable in Options for large models
COUNT(), COUNTROWS(), DISTINCTCOUNT()
Aggregation Measures: Single value results
from a column Date column in the date table to date column
SUMX(), AVERAGEX(), MAXX(), MINX() Relationship
in the fact table
Iterators: Functions that evaluate an expression
Example: SUMX(Sales, Sales[Quantity] * row by row
Sales[Price])
Static: Fixed DAX filter written into the role Example: [Region] = "West"
Right click a table, then New quick measure Location Role Types
Uses USERPRINCIPALNAME() or USERNAME()
Aggregate per category, Filters, Time Dynamic: Filter based on the signed in user
intelligence, Totals Example: Security table mapping email to region
Quick Measures: Prebuilt DAX templates
Categories
created through a dialog
Mathematical operations, Text Desktop: Modeling tab, then Manage roles
Result is editable DAX Create a role and add a table filter expression
Roles: Rules that restrict which rows a user can
CALCULATE(expression, filter1, filter2) Syntax Row-Level Security see
Setup Test with View as
Example: Sales[Region] = "West" Boolean filter: Simple column condition Service: Assign members under semantic model
Filter Arguments Security
FILTER() evaluates row by row Table filter: Filter returned by a table function
Applies to the Viewer workspace role only
ALL(): Removes filters from a table or columns
Admin, Member, and Contributor bypass the
ALLEXCEPT(): Removes all filters except the rules
listed columns Behaviour
CALCULATE(): Evaluates an expression with a Filters follow relationship direction
REMOVEFILTERS(): Clears filters inside modified filter context
Filter Modifiers
CALCULATE() only Apply security filter in both directions extends
the flow
KEEPFILTERS(): Keeps existing filters instead of
replacing them
USERELATIONSHIP(): Activates an inactive
relationship
Create DAX Calculations
Converts row context into filter context
Context Transition
Occurs when CALCULATE() runs inside a row
context
Current row being evaluated
Row Context
Created by calculated columns and iterators
Row Context vs Filter Context: Frequent exam
comparison
Set of filters applied to the calculation
Filter Context
Created by slicers, visuals, relationships, and
CALCULATE()
Marked date table with a continuous date
Prerequisite
column
TOTALYTD(): Year to date total
TOTALQTD(): Quarter to date total Period to Date
Time Intelligence: Calculations that compare or
TOTALMTD(): Month to date total
accumulate over dates
SAMEPERIODLASTYEAR(): Shifts the dates back
one year
DATEADD(): Shifts dates by a chosen interval
Period Comparison
PARALLELPERIOD(): Returns a full parallel
period
Example: CALCULATE([Total Sales],
SAMEPERIODLASTYEAR('Date'[Date]))
Account balances and inventory levels Typical Cases
Take the last value in the period instead of Semi-Additive Measures: Values that sum
summing across some dimensions but not time
CLOSINGBALANCEMONTH(),
Approach
CLOSINGBALANCEYEAR()
LASTDATE() inside CALCULATE()
Example: Month end stock level
MEDIAN(): Middle value of a column
STDEV.S() and STDEV.P(): Standard deviation
for sample or population
VAR.S() and VAR.P(): Variance for sample or Statistical Functions: Basic calculations on
population spread and ranking
RANKX(): Ranks values within a table
TOPN(): Returns the top rows by an expression
DIVIDE(): Division that handles zero
denominators safely
RELATED(): Pulls a value from the one side of a
relationship
Supporting Functions: Frequently combined
SWITCH(): Evaluates several conditions in with measures
sequence
VAR stores a value, RETURN outputs the result
Variables
Improves readability and performance
