Simple Linear Regression
Hi
I am using your Simple Linear Regression measure with great success. Thank You.
I am doing temperature studies and I want to find the first and last values of your measure in a given time interval which is filtered by a slicer. By doing this I can calculate the difference in the trend line over the interval of filtered years.
I can find the first and last year of the filtered interval by using FIRSTNONBLANK and LASTNONBLANK but I can't do this with your code because FIRSTNONBLANK and LASTNONBLANK only accept a column as an argument. I cannot seem to convert your measure into a calculated column to do this. Is there a version of your measure that produces a column? My data has two columns year and temperature. X and Y.
Any help would be much appreciated.
Tony Maclaren
Thanks for your feedback, Tony!
If I understand you correctly, there are a few ways to achieve your goal.
Here is an example of a measure:
Starting Temperature = VAR Known = FILTER ( SELECTCOLUMNS ( ALLSELECTED ( 'Table'[Year] ), "Known[X]", 'Table'[Year] ), "Known[Y]", [Temperature] ), AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) ) ) VAR First = TOPN ( 1, Known, Known[X], ASC ) RETURN MINX ( First, Known[Y] )Dear Daniil
Thank you for your very prompt response. I will be in Sydney, where I grew up, in early December!
Here is the tweaked code below (minus some brackets) that does the trick.Thank you so much.
This is an incredibly useful companion measure to your Simple Linear Regression measure, and because it works with measures it will find the equivalent of FIRSTNONBLANK in a measure. However it does not work correctly in the filtered context of a YEAR slider to filter the interval of years which the Simple Linear Regression measure does .The value changes with the slider, but the results do not correctly match the estimated starting value of the Estimated measure except for the first value of the whole dataset.
Starting Temperature =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( 'cetdata'[YEAR] ),
"Known[X]", 'cetdata'[YEAR],
"Known[Y]", [Estimated]
),
AND (
NOT ( ISBLANK ( Known[X] ) ),
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR First =
TOPN ( 1, Known, Known[X], ASC )
RETURN
MINX ( First, Known[Y] )
NOT ( ISBLANK ( Known[Y] ) )
)
)
VAR First =
TOPN ( 1, Known, Known[X], ASC )
RETURN
MINX ( First, Known[Y] )Now we have to fix the filter context and figure out how to find [Ending temperature] so we can subtract [Starting Temperature] and get the trend change per filtered interval.
Thanks again--great stuff. No one else had a clue what my problem was or how to solve it.
Tony
Tony, you should visit the local Power BI User Group if you get a chance :-) The next meeting date should be announced at Meetup soon.
Try the following measures:
Starting Temperature = VAR Estimate = SELECTCOLUMNS ( KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ), "Estimate[X]", 'cetdata'[YEAR], "Estimate[Y]", [Estimated] ) VAR First = TOPN ( 1, Estimate, Estimate[X], ASC ) RETURN MINX ( First, Estimate[Y] )Ending Temperature = VAR Estimate = SELECTCOLUMNS ( KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ), "Estimate[X]", 'cetdata'[YEAR], "Estimate[Y]", [Estimated] ) VAR Last = TOPN ( 1, Estimate, Estimate[X], DESC ) RETURN MAXX ( Last, Estimate[Y] )Temperature Difference = [Ending Temperature] - [Starting Temperature]
