Power BI Report Performance on Redshift database

[Anonymous]

Hello Everyone,

I am pulling the data into the Power bi using redshift database and direct query connection.
I am having around 16 GB of data in the dataset where i am getting very low performance of the Power BI report.
Requirment was to have a dynamic filter for the user where based on the selection measure value should get change
dynamically.

EX:If selected value of col1=KG= and selected value of col2 ="XYZ" then [Measure1],simillarly we
have 50 + conditions in the same measure where based on the selected value measure value will change in the graph.

Please suggest how to improve the performance oft he report.

Thanks,

Anand Gavle

[v-jayw-msft]

Hi @Anonymous ,

So you have quite large dataset and quite much conditions. Please consider to reduce data and upgrade server hardware if it's possible.

Besides, I don't quite understand the example you provided for the measure.

If you have multiple measures based on the conditions like:

if conditions1 then measure1, if conditions2 then measure2 etc...

Then you could try to use SWITCH() function like:

switch(true,condition1,measure1,condition2,measure2,etc...)

 If you want to calculate measure1 based on multiple conditions, please consider to filter the table before the calculation.

var table1 = filter(table1,conditions)
var table2 = filter(table2,conditions)
return
calculation

Best Regards,

Jay

[Anonymous]

Hi,

Appreciate your suggestion.

I have tried the switch statement already the way you have suggested but i dont see the difference in the performance.

Thanks,

Anand Gavle

[Fowmy]

@Anonymous 

I think Dynamic Query Parameters should be of help in solving your problem:

Refer to these articles: 

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[Anonymous]

Hi Team

When i am having only one visual on the page which is showing for area wise forecast and actual values.I checked in the Performance Analyser-->Start recording -->Refresh Visuals-->then i am looking at the DAX query generated at the backend there for the visuals which show group by area for actual and forecast which is correct.Now i login to the AWS application-->Under services (Amazon Redshift)-->Queries-->Filtering All queries then i can see the SQL there which is having all the columns of the forecast and sales table under select statement grouping by area.Here in redshift we are suppose to get on the columns which i have on the visual right.

Thanks,

Anand Gavle