Filter context issues with LINESTX

[AlexisOlson]

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 

LINEST Error.pbix

[QueryQueen]

@jeffrey_wang I am also experiencing this issue. Is it possible to track the progress on your bug submission? Has there been any root cause analysis or resolution plan?

[anselmojg]

Hope to get this fixed soon... I spent hours wondering if all of a sudden I had forgotten DAX!

Maybe unrealted but wondering why intelisense does not recognize the names of the fields in the table generated by LINEST/X, like [Slope1]....

[keshen_msft]

The issue is fixed, and the fix will be included in Desktop June release. Thank you for reporting the issue!

[Daniil]

@AlexisOlson, while we're waiting for the June release of Power BI Desktop, here's a workaround -- use TOPN:

LINEST_Intercept Daniil = 
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 ( TOPN ( 1, Coeffs ), [Intercept] )
RETURN
    Intercept

[AlexisOlson]

@Daniil Thanks for the suggestion but it doesn't appear to resolve the error for me.

[Daniil]

@AlexisOlson my apologies, I didn't test properly. I must've used a table visual with ID added, which isn't what you want of course. Guess we've got to wait for the June release for LINESTX to work well.

[AlexisOlson]

I have confirmed that this issue is fixed in the June 2023 Power BI release. 😃

[keshen_msft]

@AlexisOlson , thank you for the confirmation!