Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to create individual linear interpolations on multiple data series, and then average the resulting interpolations. I have a simple data set below, but my actual data set is very large with hundreds of series' and thousands of values.
Series | X Value | Y Value |
Series 1 | 2 | 20 |
Series 1 | 4 | 20 |
Series 1 | 6 | 20 |
Series 1 | 8 | 20 |
Series 2 | 3 | 30 |
Series 2 | 6 | 30 |
Series 2 | 9 | 30 |
Series 2 | 12 | 30 |
I have created a separate DIM table with a column of numbers from 1 to 12 that I would like to interpolate against as my x-axis. I would like to create a measure that interpolates each series, and would like to average the resulting interpolations. Any thoughts would be greatly appreciated.
The Linear Interpolation Post by @gregdeckler has gotten me close, but only meets the intended purpose if I have one series of data. Thanks in advance for any insights!!
Mike
Solved! Go to Solution.
@mcstunner Yikes! That formula is due for an overhaul anyway. Try this, PBIX is attached below signature.
Series 1 =
VAR __Table = FILTER('Table',[Series] = "Series 1")
VAR __x3 = [Column1]
VAR __match = MAXX(FILTER(__Table,[X Value] = __x3),[Y Value])
VAR __x1 = MAXX(FILTER(__Table,[X Value] < __x3),[X Value])
VAR __x2 = MINX(FILTER(__Table,[X Value] > __x3),[X Value])
VAR __y1 = MAXX(FILTER(__Table,[X Value] = __x1), [Y Value])
VAR __y2 = MAXX(FILTER(__Table,[X Value] = __x2), [Y Value])
VAR __Result = IF( __match <> BLANK(),__match, __y1 + DIVIDE((__x3 - __x1) * (__y2 - __y1), (__x2 - __x1)))
RETURN
__Result
Series 2 =
VAR __Table = FILTER('Table',[Series] = "Series 2")
VAR __x3 = [Column1]
VAR __match = MAXX(FILTER(__Table,[X Value] = __x3),[Y Value])
VAR __x1 = MAXX(FILTER(__Table,[X Value] < __x3),[X Value])
VAR __x2 = MINX(FILTER(__Table,[X Value] > __x3),[X Value])
VAR __y1 = MAXX(FILTER(__Table,[X Value] = __x1), [Y Value])
VAR __y2 = MAXX(FILTER(__Table,[X Value] = __x2), [Y Value])
VAR __Result = IF( __match <> BLANK(),__match, __y1 + DIVIDE((__x3 - __x1) * (__y2 - __y1), (__x2 - __x1)))
RETURN
__Result
@mcstunner Yikes! That formula is due for an overhaul anyway. Try this, PBIX is attached below signature.
Series 1 =
VAR __Table = FILTER('Table',[Series] = "Series 1")
VAR __x3 = [Column1]
VAR __match = MAXX(FILTER(__Table,[X Value] = __x3),[Y Value])
VAR __x1 = MAXX(FILTER(__Table,[X Value] < __x3),[X Value])
VAR __x2 = MINX(FILTER(__Table,[X Value] > __x3),[X Value])
VAR __y1 = MAXX(FILTER(__Table,[X Value] = __x1), [Y Value])
VAR __y2 = MAXX(FILTER(__Table,[X Value] = __x2), [Y Value])
VAR __Result = IF( __match <> BLANK(),__match, __y1 + DIVIDE((__x3 - __x1) * (__y2 - __y1), (__x2 - __x1)))
RETURN
__Result
Series 2 =
VAR __Table = FILTER('Table',[Series] = "Series 2")
VAR __x3 = [Column1]
VAR __match = MAXX(FILTER(__Table,[X Value] = __x3),[Y Value])
VAR __x1 = MAXX(FILTER(__Table,[X Value] < __x3),[X Value])
VAR __x2 = MINX(FILTER(__Table,[X Value] > __x3),[X Value])
VAR __y1 = MAXX(FILTER(__Table,[X Value] = __x1), [Y Value])
VAR __y2 = MAXX(FILTER(__Table,[X Value] = __x2), [Y Value])
VAR __Result = IF( __match <> BLANK(),__match, __y1 + DIVIDE((__x3 - __x1) * (__y2 - __y1), (__x2 - __x1)))
RETURN
__Result
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |