power query python script and slicers not properly interacting in report

[Anonymous]

Hello,

     I have python visuals in a report that contain the scripting right in the visual the way it normally would be done. I also use a pythonscript to run a statistical regression being on data and giving me back results in a new data table in power query. I take those results and place them into 'card' visual in my report along side the visuals. 

     The problem: when I put a slicer on the page that has a time range in it, it affects the visuals that are scripted right in the report just fine, however the calculation done in power queryy and displayed through 'card' visuals are not being affected correctly. 

     My own analysis thus far: the calculations do store values such as R squared, which is realy a single calculation done on the entire data set (so a value on each line isn't technically correct), on each line because it needs be stored as a table in power query (i'm assuming this is the reason). this explains why the number isn't changing when narrowing down the time with the slicer, because every year has the same value stored in it for the R squared calculation. What would make it correct is if the slicer caused a recalcualtion to be done by power query with just the selected data to reproduce an accurate calculation. 

This the report before changing the slicer. at this point the 'card' visuals are accurate.This is after changing the slicer to limit the data be analysed, the graph plots do adapt properly but as you can see the card values remain the same, which is not correct.

here the python script from power query:

# 'dataset' holds the input data for this script

import pandas
import numpy as np
import statsmodels.api as sm
import matplotlib.pyplot as plt

y = dataset['Touchdowns']
x1 = dataset['Total Rushing Yds']
x2 = dataset['Total Passing Yds']
x = np.column_stack((x1,x2))
x = sm.add_constant(x,prepend=True)
model = sm.OLS(y,x)
results = model.fit()
dataset['R2'] = results.rsquared
dataset['Adj R2'] = results.rsquared_adj
dataset['F Stat'] = results.fvalue
dataset['Constant'] = results.params.const
dataset['X1'] = results.params.x1
dataset['X2'] = results.params.x2
dataset['residuals'] = results.resid_pearson

[TomMartens · Accepted solution]

Hey,

you have to rethink your current solution, a  Power Query (no matter if it contains python script or not) is only "executed" if the data is refreshed and not if the selection of a slicer is changed. This will only affect the number of rows meaning, tables are filtered. A visual just represents the filtered table, but the table will not be recalculated, this is also valid for calculated columns created by using DAX statements.

Only measures are "recalculated" if a user interacts with the data model.

Regards,

Tom