Power BI LINEST and LINESTX DAX Functions

Both functions perform linear regression (least-squares method) to calculate statistics for a straight line fitting your data. Here’s how they work:

---

1. LINEST Function

Fits a line using columns from a single table.

Syntax:

LINEST ( 
    Y_column, 
    X_column, 
    [const], 

[stats]

)

Parameters:

• Y_column: Dependent variable column (e.g., Sales)
• X_column: Independent variable column(s) (e.g., Advertising Spend)
• [const] (Optional):
• TRUE/1: Include intercept (default)
• FALSE/0: Force intercept to zero
• [stats] (Optional):
• TRUE/1: Return additional statistics (R², F-statistic, etc.)
• FALSE/0: Return only coefficients (default)

---

2. LINESTX Function

More flexible—works with tables filtered by DAX expressions or multiple tables.

Syntax:

LINESTX ( 
    Table, 
    Y_expression, 
    X_expression, 
    [const], 

[stats]

)

Parameters:

• Table: Table containing the data
• Y_expression: DAX expression for the dependent variable
• X_expression: DAX expression for the independent variable(s)

---

Examples

Example 1: Simple Linear Regression with LINEST

Data:

Month	Advertising Spend	Sales
Jan	1000	15000
Feb	2000	25000
…	…	…

DAX:

Simple Regression = 
LINEST (
    Sales[Sales],          // Y-axis: Sales
    Sales[Advertising Spend] // X-axis: Spend
)

Output (Single-row table):

Slope	Intercept
10	5000

Equation:
Sales = 10 * Advertising Spend + 5000

---

Example 2: Multiple Regression with LINESTX

Goal: Predict Sales using Advertising Spend and Discount %.
DAX:

Multiple Regression = 
LINESTX (
    Sales,                         // Table
    Sales[Sales],                   // Y: Sales
    Sales[Advertising Spend],       // X1: Spend
    Sales[Discount %]               // X2: Discount
)

Output:

Slope_Spend	Slope_Discount	Intercept
9.5	-1000	6000

Equation:
Sales = 9.5 * Spend - 1000 * Discount % + 6000

---

Example 3: Return Advanced Statistics

DAX:

Regression Stats = 
LINESTX (
    Sales,
    Sales[Sales],
    Sales[Advertising Spend],
    TRUE,   // Include intercept
    TRUE    // Return full statistics
)

Output (Partial):

Slope	Intercept	R²	StdError	F-statistic	…
10	5000	0.95	120	450	…

---

Example 4: Forced Zero Intercept (const=FALSE)

DAX:

Zero Intercept = 
LINEST (
    Sales[Sales],
    Sales[Advertising Spend],
    FALSE  // Force intercept=0
)

Output:

Slope
12.5

Equation:
Sales = 12.5 * Advertising Spend

---

Key Differences:

Feature	LINEST	LINESTX
Input Flexibility	Columns only	DAX expressions
Table Handling	Single table	Works with filtered tables/context
Use Case	Simple column-based regression	Complex models (e.g., time filters)

---

Practical Tips:

1. Output is a Table: Always store results in a calculated table or use with SELECTCOLUMNS.
2. Dynamic Analysis: Combine LINESTX with CALCULATETABLE to run regression on filtered data.
3. Slope Order: Coefficients appear in the order of X-columns provided.
4. Statistics Columns: When stats=TRUE, output includes 10+ columns (e.g., R², standard errors).

Real-World Use Cases:

• Forecast sales based on marketing spend.
• Analyze pricing elasticity.
• Quantify impact of promotions/discounts.