Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Filter context issues with LINESTX

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:

AlexisOlson_0-1676669577277.png

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:

AlexisOlson_1-1676669757888.png

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:

AlexisOlson_3-1676670548825.png

 

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 

Status: Accepted

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

Comments
QueryQueen
Regular Visitor

@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
Advocate II

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
Microsoft Employee

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

Daniil
Kudo Kingpin

@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
Super User

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

AlexisOlson_0-1685025473874.png

 

Daniil
Kudo Kingpin

@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
Super User

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

keshen_msft
Microsoft Employee

@AlexisOlson , thank you for the confirmation!