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

[jeffrey_wang]

When you add a filter on a single [ID], VALUES(Data[ID]) returns only that ID which is not what you want. Change to CalculateTable(Values(Data[ID]), All(Data[ID])) to remove the filter on the ID column but keep all other filters. If you always want to calculate the coefficient based on all IDs, simply use All(Data[ID]).

[Greg_Deckler]

@AlexisOlson Interesting and a bit non-sensical. Even if you override the slicer to bring all ID's back into context, the measure still breaks. For example:

LINEST_Intercept 2 = 
VAR __Group = MAX('Data'[Group])
VAR Summary = SUMMARIZE(FILTER(ALL(Data),[Group] = __Group), [ID], "@Y",  SUM ( Data[Y] ), "@X1", SUM (Data[X1] ), "@X2", SUM(Data[X2] ) )
VAR Coeffs = LINESTX ( Summary, [@Y], [@X1], [@X2] )
VAR Intercept = SELECTCOLUMNS ( Coeffs, "Intercept", [Intercept] )
RETURN
    Intercept

and:

LINEST_Intercept 3 = 
    VAR __Group = MAX('Data'[Group])
    VAR Coeffs = LINESTX ( FILTER(ALL(Data), [Group] = __Group), [Y], [X1], [X2] )
    VAR Intercept = SELECTCOLUMNS ( Coeffs, "Intercept", [Intercept] )
RETURN
    Intercept

Same problem. Mystifying as filtering on Group for example does not exhibit this behavior. Same also happens if you filter on Y, X1 or X2. BUT! If you first filter on Group and then filter ID, Y, X1 or X2 then it works if you only have a single Group selected. Although in that case you lose your Group in the table. Wacky.

[Oriolgaldon]

If the data is filtered in such a way that not all the necessary IDs are included for linear regression calculation, the results will be incorrect. I think incorporating "ALL" would solve the issue.

[AlexisOlson]

@jeffrey_wang, @Greg_Deckler, @Oriolgaldon
The way I intend to use LINESTX in practice is for calculating betas and alphas of investments like hedge funds over different time periods. In my simplified example, Group plays the role of a specific investment, ID corresponds to a month-end date, Y is the return of the investment for that month, and X1 and X2 are monthly returns of factors I want to regress against.

Obviously, regression on a single data point doesn't make sense but I do need to be able to filter over different periods, like trailing 3 years or 5 years. This is why I tested filtering on the range 1-4 (not a single ID value). I need the calculation to work for a subset of the IDs, not just all of them.

I can get it to work exactly like I expect it to if I calculate the coefficient manually rather than with LINESTX. Here's a screenshot of an example closer to my actual use case:

In this example, LINESTX works fine when setting the period one at a time but returns blanks if it's used as a dimension with multiple values.

[jeffrey_wang]

Why don't you do some debugging by making the measure return the Summary table to see if it contains the right set of rows for the regression calculation?

Instead of

    return intercept

at the end,

    return tocsv(Summary, -1)

instead and show the result in a table visual.

[AlexisOlson]

@jeffrey_wang

TOCSV ( Summary, -1 ) behaves as I expect with no ID filtering and with ID IN {1,2,3,4}.

TOCSV ( Coeffs, -1 ) behaves as I expect with no ID filtering but returns empty tables with ID IN {1,2,3,4}, except for the Total row.

If I filter out ID = 5 in the query editor before loading the model (rather than via a slicer), then there's no problem.

I spent several hours trying to debug this before I posted, both in Power BI Desktop and using the debug feature in Tabular Editor 3 and your suggestion was one of the first things I tried. I didn't just run into a problem and post an issue without doing any of my own research. Instead, I created a simple example demonstrating the strange behavior so that others can quickly and easily reproduce it.

I'm certainly open to debugging suggestions but I'm getting the sense that you didn't open the pbix I included with the original post. There's clearly something weird going on here as @Greg_Deckler has confirmed. All other replies so far seem to assume I'm clueless.

[jeffrey_wang]

Sorry I didn't read every single word of your original post. Every "unexpected error" is a product bug. I have logged the bug on your behalf.

[paul_levchuk]

I was curious so I downloaded the example and played a little bit.

0/ When I tried to open the file, some window quickly popped up and disappeared. I believe Power Pivot generated an exception that was not intercepted by Power BI Desktop.

1/ If I clear ID slicer then LINESTX() indeed is working.

2/ BUT, If I select 3 groups in Group slicer, then clear ID slicer, and then filter ID by Power BI filter pane everything is working fine.

So I think there is incorrect interaction between slicers and LINESTX() and as result there is a memory exception.

[Anonymous]

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

[Yanant1020]

Hello, has anyone found workarounds for this so slicers work with LINESTX?

I am so excited that LINESTX was added and was so disappointed to find out it doesn't work with slicers. I ran a lot of experiments to make sure it wasn't an error in my DAX before coming to post a bug and then I found this bug already exists. LINEST and LINESTX are hugely powerful and bring Power BI's analytical capabilities a giant leap forward.

Also if I can be of any help by sharing my code and where it stops working please let me know. After digging into the queries from the performance analyzer it seems to have to do with filters in the SUMMARIZECOLUMNS() that come from the TREATAS() filter table. Not sure why this combination of functions fails to produce a result but that's where I got to.

DEFINE
    MEASURE '0 - Measures'[Test1] =
        VAR _SelectedMonth = [Selected Calendar Month Index]
        VAR _LinReg =
            LINESTX (
                CALCULATETABLE (
                    SUMMARIZE (
                        'FACT - Bill To Customer Status',
                        'DIM - Date'[MMM YYYY],
                        "_MonthIndex",
                            _SelectedMonth - SELECTEDVALUE ( 'DIM - Date'[Calendar Month Index] ) + 35,
                        "_CustomersAcquired", [New Bill To Customers Acquired - 3 Year Lag]
                    ),
                    FILTER (
                        ALL ( 'DIM - Date' ),
                        AND (
                            'DIM - Date'[Calendar Month Index] <= _SelectedMonth + 35,
                            'DIM - Date'[Calendar Month Index] >= _SelectedMonth
                        )
                    )
                ),
                [_CustomersAcquired],
                [_MonthIndex]
            )
        RETURN
            SELECTCOLUMNS ( _LinReg, "Slope", [Slope1] )
    VAR __DS0FilterTable =
        TREATAS ( { "Mar 2023" }, 'FLOAT - Month'[MMM YYYY] )
    VAR __ValueFilterDM3 =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'DIM - Sales Office'[Sales Office Code - Name],
                    'DIM - Territory'[Territory Code - Current Owner Full Name],
                    __DS0FilterTable,
                    "New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month", '0 - Measures v2'[New Bill To Customers Acquired - Trailing Rolling 12 Month],
                    "New_Bill_To_Customers_Acquired___Prior_Trailing_Rolling_12_Month",
                        '0 - Measures v2'[New Bill To Customers Acquired - Prior Trailing Rolling 12 Month],
                    "New_Bill_To_Customers_Acquired_____Change___Prior_R12_vs_R12", '0 - Measures v2'[New Bill To Customers Acquired - % Change - Prior R12 vs R12],
                    "New_Bill_To_Customers_Acquired___3_Year_Lag___Least_Squares_Slope",
                        '0 - Measures v2'[New Bill To Customers Acquired - 3 Year Lag - Least Squares Slope],
                    "Test Slope", [Test1],
                    "Amount", IGNORE ( '0 - Measures'[Amount] )
                )
            ),
            NOT ( ISBLANK ( [Amount] ) )
        )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL (
                'DIM - Sales Office'[Sales Office Code - Name],
                "IsGrandTotalRowTotal"
            ),
            __DS0FilterTable,
            __ValueFilterDM3,
            "New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month", '0 - Measures v2'[New Bill To Customers Acquired - Trailing Rolling 12 Month],
            "New_Bill_To_Customers_Acquired___Prior_Trailing_Rolling_12_Month",
                '0 - Measures v2'[New Bill To Customers Acquired - Prior Trailing Rolling 12 Month],
            "New_Bill_To_Customers_Acquired_____Change___Prior_R12_vs_R12", '0 - Measures v2'[New Bill To Customers Acquired - % Change - Prior R12 vs R12],
            "New_Bill_To_Customers_Acquired___3_Year_Lag___Least_Squares_Slope",
                '0 - Measures v2'[New Bill To Customers Acquired - 3 Year Lag - Least Squares Slope],
            "Test Slope", [Test1]
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core,
            [IsGrandTotalRowTotal], 0,
            [New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month], 0,
            'DIM - Sales Office'[Sales Office Code - Name], 1
        )
    VAR __DS0CoreNoInstanceFiltersNoTotals =
        FILTER ( KEEPFILTERS ( __DS0Core ), [IsGrandTotalRowTotal] = FALSE )

EVALUATE
__ValueFilterDM3
//__DS0PrimaryWindowed
//ORDER BY
//    [IsGrandTotalRowTotal] DESC,
//    [New_Bill_To_Customers_Acquired___Trailing_Rolling_12_Month] DESC,
//    'DIM - Sales Office'[Sales Office Code - Name]

Removing the portions called out in red returns correct results: