cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## LINESTX with slicer

Hello,

I am trying to calculate trends using slope and intercept

I am using the following DAX, which is functioning well. However, the issue arises when I have a slicer on the order date. I want the dataset for the query below to also get filtered for that date range when a user applies a start and end date filter on the order date slicer

Revenue trend = VAR line =

LINESTX (
ALL('Sales order line fact'[Order date] ),
[Total revenue trend],
'Sales order line fact'[Order date]
)
VAR slope = SELECTCOLUMNS ( line, [Slope1] )
VAR intercept = SELECTCOLUMNS ( line, [Intercept] )
VAR x = SELECTEDVALUE ( 'Sales order line fact'[Order date] )
VAR y = x * slope + intercept
RETURN y

1 ACCEPTED SOLUTION
Memorable Member

@Ethanhunt123 Hi! Try with:

Revenue trend =
VAR line =
LINESTX (
ALLSELECTED('Sales order line fact'[Order date]), -- This respects the slicer
[Total revenue trend],
'Sales order line fact'[Order date]
)
VAR slope = SELECTCOLUMNS(line, [Slope1])
VAR intercept = SELECTCOLUMNS(line, [Intercept])
VAR x = SELECTEDVALUE('Sales order line fact'[Order date])
VAR y = x * slope + intercept
RETURN y

BBF

4 REPLIES 4
Helper IV

I was doing something incorrectly. Your solution worked perfectly. Thank you so much!  🙂

Memorable Member

@Ethanhunt123 Hi! Try with:

Revenue trend =
VAR line =
LINESTX (
ALLSELECTED('Sales order line fact'[Order date]), -- This respects the slicer
[Total revenue trend],
'Sales order line fact'[Order date]
)
VAR slope = SELECTCOLUMNS(line, [Slope1])
VAR intercept = SELECTCOLUMNS(line, [Intercept])
VAR x = SELECTEDVALUE('Sales order line fact'[Order date])
VAR y = x * slope + intercept
RETURN y

BBF

Helper IV

I have tried this but I don't think so this is working see below

Data without the filter on date

Data with the filter on the date  - From Jan 1 till Sept

Values are the same as above. My assumption is if I am changing dataset then values should also get change

Memorable Member

@Ethanhunt123 Can you share the pbix?

BBF

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors