PL-300: Model the Data (25-30%)

Fact Tables

Contain transactional, measurable data (e.g., Test Results, Sales)

Dimension Tables

Contain descriptive attributes (e.g., Date, Product, Customer)

CALCULATE

Evaluates an expression in a modified filter context.

SAMEPERIODLASTYEAR

Returns a set of dates in the same period from the previous year.  

TOTALYTD

Calculates the sum from the start of the year to the selected date (Ex:TOTALYTD(Sales[Amount],Date[Date] )

DATESBETWEEN

Creates a range between two dates, useful for rolling totals (e.g., a 31-day rolling average).

TOPN

Returns the top N rows based on an expression ($TOPN(8,Product,[Sales],DESC)$ ).     

DIVIDE

Recommended for divisions to handle zero-denominator errors gracefully

PATHLENGTH

Used in parent-child hierarchies to count the number of levels from an item to the top node (e.g., Employee to CEO).

USERELATIONSHIP

Explicitly specifies which inactive relationship to use in a measure

Column Management

Remove unused columns to reduce the memory footprint. VertiPaq compression performs better with fewer unique values (low cardinality)

Data Types

Change "Text" numbers to "Whole Number" or "Decimal" types for better compression and speed

Date/Time

Split Date and Time into separate columns to improve compression by increasing the count of repeated values.

Decomposition Tree

An AI visual for ad-hoc exploration across multiple dimensions.

Key Influencers

Identifies factors that drive a specific metric (e.g., what drives high Spend).     

High-Density Sampling

Improves scatter plot performance by capturing underlying patterns in massive datasets (e.g., 10 million rows).

Performance Analyze

A tool in Desktop to measure the duration of DAX queries and visual display times.