Forum Discussion
Power BI - Trendline exploration
Hi Linuu ,
Yes, this approach can generally work in DirectQuery mode as well. Since the regression logic is being applied on aggregated results rather than raw row-level data, it is usually compatible with DirectQuery models.
In some cases, errors can occur when the DAX logic becomes too complex for DirectQuery processing, especially when multiple table transformations or intermediate calculations are involved.
A few workarounds that may help in this scenario are using a numeric sequence column instead of relying only on a text-based Month-Year field, keeping the calculations measure-based and as simple as possible, and building/testing the logic incrementally when introducing more advanced calculations.
For more information, please refer to the official documentation:
-
Analytics pane:
https://learn.microsoft.com/power-bi/transform-model/desktop-analytics-pane -
DirectQuery limitations:
https://learn.microsoft.com/power-bi/connect-data/desktop-directquery-about
If possible, please share the exact DAX error message or the measure definition you are using so the issue can be narrowed down further.
Hope this helps.
Best Regards,
Abdul Rafi-
Super UserHi Linu
Attached is a PBIX file containing a small illustration of one way you could set this up.
I have used DAX user-defined functions as they provide a nice way of setting up the logic so that it can be applied to different combinations of columns/measures. You don't have to use functions however.
Firstly, I created the below functions (shown using Tabular Editor 3 DAX script).
The idea with these functions is that you can pass them a column reference and a measure and they will return the relevant result. The function Linest.EquationText also takes two arguments specifying the format strings to apply.
--------------------------
-- Function: Linest.Result
--------------------------
FUNCTION Linest.Result =
( X_Dimension: COLUMNREF, Y_Measure: MEASUREREF ) =>
VAR BaseTable =
SELECTCOLUMNS (
CALCULATETABLE (
SUMMARIZECOLUMNS (
X_Dimension,
"@Y", Y_Measure
),
ALLSELECTED ( TABLEOF ( X_Dimension ) )
),
"@X", X_Dimension,
"@Y", [@Y]
)
VAR LinestResult = LINESTX ( BaseTable, [@Y], [@X], TRUE ( ) )
RETURN
LinestResult-----------------------------
-- Function: Linest.Intercept
-----------------------------
FUNCTION Linest.Intercept =
( X_Dimension: COLUMNREF, Y_Measure: MEASUREREF ) =>
VAR LinestResult = Linest.Result ( X_Dimension, Y_Measure )
VAR LinestIntercept =
SELECTCOLUMNS ( LinestResult, [Intercept] )
RETURN
LinestIntercept-------------------------
-- Function: Linest.Slope
-------------------------
FUNCTION Linest.Slope =
( X_Dimension: COLUMNREF, Y_Measure: MEASUREREF ) =>
VAR LinestResult = Linest.Result ( X_Dimension, Y_Measure )
VAR LinestSlope = SELECTCOLUMNS ( LinestResult, [Slope1] )
RETURN
LinestSlope----------------------------
-- Function: Linest.Estimate
----------------------------
FUNCTION Linest.Estimate =
( X_Dimension: COLUMNREF, Y_Measure: MEASUREREF ) =>
VAR LinestResult = Linest.Result ( X_Dimension, Y_Measure )
VAR X_Min =
CALCULATE (
FIRSTNONBLANK (
X_Dimension,
Y_Measure
),
ALLSELECTED ( TABLEOF ( X_Dimension ) )
)
VAR X_Max =
CALCULATE (
LASTNONBLANK (
X_Dimension,
Y_Measure
),
ALLSELECTED ( TABLEOF ( X_Dimension ) )
)
VAR X_Current = MAX ( X_Dimension )
VAR Estimate =
IF (
AND ( X_Current >= X_Min, X_Current <= X_Max ),
VAR LinestIntercept =
SELECTCOLUMNS ( LinestResult, [Intercept] )
VAR LinestSlope = SELECTCOLUMNS ( LinestResult, [Slope1] )
RETURN
LinestIntercept + LinestSlope * X_Current
)
RETURN
Estimate--------------------------------
-- Function: Linest.EquationText
--------------------------------
FUNCTION Linest.EquationText =
(
X_Dimension: COLUMNREF,
Y_Measure: MEASUREREF,
Format_Intercept: STRING,
Format_Slope: STRING
) =>
VAR LinestResult = Linest.Result ( X_Dimension, Y_Measure )
VAR LinestIntercept =
SELECTCOLUMNS ( LinestResult, [Intercept] )
VAR LinestSlope = SELECTCOLUMNS ( LinestResult, [Slope1] )
VAR EquationText =
NAMEOF ( Y_Measure, MEASURE, ESCAPED ) & " = "
& FORMAT ( LinestIntercept, Format_Intercept )
& FORMAT ( LinestSlope, Format_Slope )
& " × "
& NAMEOF ( X_Dimension, COLUMN, ESCAPED )
RETURN
EquationText
Having defined these functions, I then created these measures:
Intercept - Average Sales Price by Month =
Linest.Intercept ( 'Date'[End of Month], [Average Sales Price] )Slope - Average Sales Price by Month =
Linest.Slope ( 'Date'[End of Month], [Average Sales Price] )Estimate - Average Sales Price by Month =
Linest.Estimate ( 'Date'[End of Month], [Average Sales Price] )Equation - Average Sales Price by Month =
Linest.EquationText (
'Date'[End of Month],
[Average Sales Price],
"#,0.00;-#,0.00",
" + #,0.0000; − #,0.0000"
)Here is the result in some sample visuals:
Hopefully this is a useful starting point and you can apply something similar to your existing model/reports.
Thank you OwenAuger ,
only thing i nned to confirm from you ,is these formulas are direct query freindly , as i am working on direct query mode ?- OwenAuger
Linu You're welcome 🙂
The short answer is "yes", as far as I can tell!
I just ran a test at my end with a DirectQuery connection to SQL Server, and these functions and measures worked fine.
Given that
LINESTXis being passed the result ofSUMMARIZECOLUMNS, I can't see any reason why this general method wouldn't work in general for models containing DirectQuery tables/partitions.But please post back here if you have any issues.
