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: