Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I've been trying to incorporate LINESTX into my reporting but keep running into errors when modifying the filter context.
To create a minimal reproducible example, I've loaded a data table like this:
and defined a measure as follows:
LINEST_Intercept =
VAR Summary =
ADDCOLUMNS (
VALUES ( Data[ID] ),
"@Y", CALCULATE ( SUM ( Data[Y] ) ),
"@X1", CALCULATE ( SUM ( Data[X1] ) ),
"@X2", CALCULATE ( SUM ( Data[X2] ) )
)
VAR Coeffs = LINESTX ( Summary, [@Y], [@X1], [@X2] )
VAR Intercept = SELECTCOLUMNS ( Coeffs, "Intercept", [Intercept] )
RETURN
Intercept
This works fine initially in a matrix like this:
However, if I add a slicer on ID and make any selection other than the full range, I get an error on the entire visual.
Error Message:
MdxScript(Model) (4, 1) Calculation error in measure 'Data'[LINEST_Intercept]:
A table of multiple values was supplied where a single value was expected.
If I try using MAXX instead of SELECTCOLUMNS inside my measure
VAR Intercept = MAXX ( Coeffs, [Intercept] )
and filter the ID, I either get "An unexpected exception occurred" or weird results with a blank Group like this:
None of this weirdness happens if I use a different function that returns a single row like TOPN ( 1, ... ).
Please see the attached sample file.
Tagging a few people for awareness. Please ignore if you aren't interested.
@jeroenterheerdt, @marcorusso, @Greg_Deckler, @ValtteriN, @CNENFRNL, @tamerj1, @wdx223_Daniel, @smpa01
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @jeffrey_wang
I am so glad you helped with this post and look forward to your updates .
Best Regards,
Community Support Team _ Ailsa Tao