LINESTX - not able to see the intercept and slope values

Anonymous — Tue, 10 Sep 2024 10:02:06 GMT

Hello,

I have started learning how to use the LINESTX fx and Dynamic filtering.

I have a small table with 'Index', 'Gender', Event', 'Y' ( 'dependent variable' ) and 'x1' - 'x4' (independent variables). At the bottom I have added a snippet of the table I am working with.

I would like that when using the event and/or gender slicers, the correct values (i.e slope and intercept) is calculated.

The measure I am using is:

PREDICTION =

VAR line = LINESTX (

ALLSELECTED ('LinearRegression'[X1]), --to keep slicers

[Total_Y], --Expression for Y axis

'LinearRegression'[X1] ---Expression for X axis

)

VAR slope = SELECTCOLUMNS(line, [Slope1])

VAR intercept = SELECTCOLUMNS(line, [Intercept])

VAR x = SELECTEDVALUE('LinearRegression'[X1])

VAR y = x * slope + intercept

RETURN y

This measure seems to give me a dynamic value so I am assuming is working, however, I would like to see the value calculated for Slope1 and Intercept, and here is where my issue is. When I 'isolate' the measure that is supposed to get me these values it just doesn't work. The 'isolated'measure I mean is :

SlopeIntercept = LINESTX (

ALLSELECTED ('LinearRegression'[X1]),

[Total_Y],

'LinearRegression'[X1]

)

The error message I keep getting is The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value. Same happens when I try to use the LINEST fx (LINEST ([column Y], [column x]) and use the column 'y' instead of the measure [Total_Y].

The data types in Power BI seem all to be correct. I am definitely missing something.

Any help/feedback with this is much appreciated.

Index	Gender	Event	Y	X1	X2	X3	X4
0	Female	A	118.32	28.05	58.01	29.96	88.45
1	Female	B	119.34	28.28	58.45	30.17	89.18
2	Female	C	120.17	28.51	59.06	30.55	89.77
3	Female	D	120.91	28.35	58.95	30.6	90.02
4	Female	E	124.69	29.26	60.56	31.3	92.81
5	Female	A	57.43	27.14		30.29
6	Female	B	57.44	26.48		30.96
7	Male	C	60.99	28.59		32.4
8	Male	D	61.15	29.2		31.95
9	Female	E	61.11	28.29		32.82
10	Female	A	61.67	30.12		31.55
11	Female	B	59.86	29.12		30.74
12	Male	C	121.92	27.51	59.2	31.69	90.98
13	Male	D	117.35	26.07	55.65	29.58	85.87
14	Female	E	139.91	29.64	65.01	35.37	106.91
15	Female	A	137	30.4	65.21	34.81	104.42
16	Female	B	134.52	30.08	65.23	35.15	102.99
17	Female	C	134.45	28.98	64.03	35.05	103.35

Re: LINESTX - not able to see the intercept and slope values

HotChilli — Tue, 10 Sep 2024 10:31:58 GMT

LINESTX returns a table. A measure has to be a scalar (a single) value. That's why you're getting the error.

Re: LINESTX - not able to see the intercept and slope values

FlipFlop1 — Tue, 10 Sep 2024 10:43:57 GMT

SlopeIntercept =

VAR line = LINESTX (LinearRegression,

LinearRegression[Y],

'LinearRegression'[X1]

)

VAR Inter = SELECTCOLUMNS (line, [Slope1])

RETURN

Inter

If you run the query in DAX query editor you can see that it is returning multiple columns.

The initial DAX query query does isolate the slope here, so that's why it works: VAR slope = SELECTCOLUMNS(line, [Slope1])

DEFINE

VAR line = LINESTX (LinearRegression,

LinearRegression[Y],

'LinearRegression'[X1]

)

VAR Inter = SELECTCOLUMNS (line, [Slope1])

EVALUATE

line

Re: LINESTX - not able to see the intercept and slope values

Anonymous — Tue, 10 Sep 2024 14:49:05 GMT

Thanks both for your help. I knew I was missing something - LINESTX returning a table!

I can see all values now.

topic Re: LINESTX - not able to see the intercept and slope values in DAX Commands and Tips

LINESTX - not able to see the intercept and slope values

Re: LINESTX - not able to see the intercept and slope values

Re: LINESTX - not able to see the intercept and slope values

Re: LINESTX - not able to see the intercept and slope values