LINESTX - not able to see the intercept and slope values

[Anonymous]

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

[Anonymous]

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

I can see all values now.

[FlipFlop1]

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

[HotChilli · Accepted solution]

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