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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

power query python script and slicers not properly interacting in report

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 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.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

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

1 REPLY 1
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors