i have a line chart with month-year and trAverage kw value in power bi i want show a trendline here and its slope and intersect value either on the graph or in card or kpi visual or in tooltip

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

Power BI - Trendline exploration | Microsoft Fabric Community

9 Replies

Linuu
New Member
5 months ago
I have a line chart with Month-year and Average mw values in power bi i want to show trendline and its slope and intersect value either on the graph or on card or kpi visual or in tooltip , how can i do that ?
- pankajnamekar25
  Super User
  5 months ago
  
  Hello Linuu
  
  Create a numeric Month Index column first, then use DAX linear regression formulas to calculate the Slope and Intercept. Use the equation
  
  y=mx+c
  
  to create a Trendline measure and add it as a second line in the chart. The Slope and Intercept measures can also be displayed in Card, KPI, or Tooltip visuals to show the trend direction and regression values.
  
  If my response helped you, please consider clicking
  Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
  
  Thanks,
  
  Connect with me on:
  LinkedIn |
  Data With Pankaj - YouTube
  - Linuu
    New Member
    5 months ago
    Hi Pankajnamekar ,
    Thank you
    one thing i need o confirm here , is it direct query friendly ? as i am working on direct query as of now .
    i tried writing dax but it is throwing me some errors .

OwenAuger

Super User

5 months ago

Hi 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.

Power BI - Trendline exploration.pbix1.1 MB

Linu
New Member
5 months ago
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
  Super User
  5 months ago
  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 LINESTX is being passed the result of SUMMARIZECOLUMNS, 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.

v-moharafi-msft
Community Support
5 months ago
Hi Linu ,

Thank you for reaching out to Microsoft Fabric Community and Thanks to OwenAuger and pankajnamekar25 for Sharing valuable insights.

Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Best Regards,

Abdul Rafi.
v-moharafi-msft
Community Support
4 months ago

Hi Linu ,

Could you please confirm if the issue has been resolved? If not, feel free to reach out if you have any further questions.

Your update would be helpful for other members who may face a similar issue.

Best Regards,

Abdul Rafi

Forum Discussion

Power BI - Trendline exploration

9 Replies

Related content

Trendline needed

Formula for trendline

Power BI Apps without Explore Tab

One Lake File Explorer syns issue

Polynomial Trendline