AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago
Status:
Accepted

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...
Data Modeling
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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.

Completed Ideas