Forum Discussion

Linu's avatar
Linu
New Member
4 months ago
Solved

Power BI - Trendline exploration

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
Tips and Tricks
  • v-moharafi-msft's avatar
    v-moharafi-msft
    4 months ago

    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:

    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

Linuu's avatar
Linuu
New Member
4 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's avatar
pankajnamekar25
Icon for Super User rankSuper User
4 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's avatar
    Linuu
    New Member
    4 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 .

     

    • v-moharafi-msft's avatar
      v-moharafi-msft
      Icon for Community Support rankCommunity Support
      4 months ago

      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:

      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