visuals are loading very slow in power bi

[jatneerjat]

Hi,

@Greg_Deckler @Seth_C_Bauer @Anonymous

I have a power BI report in which i am fetching data through analysis services and for that i have created measures in analysis service.Now there are some 5 'card with states' visuals which takes time while loading so what could be the reason for slow visual load ?how can i make it to load fast?

 

Sometimes this visuals(Line Chart) also took time to load

Thanks

[Seth_C_Bauer]

@jatneerjat Are you using Direct Query in your AS model? Do you have issues in both the Desktop and the Service?

[jatneerjat]

Hi @Seth_C_Bauer

I am using direct query.

Yes the issue persist in both pbix and service but in service it usually takes longer than desktop.

it takes around 2 to 3 mins sometimes 5 minutes for some cards.

and the measures i am using in cards are:

AvgVal:= IF(HASONEVALUE(Table1[Id])
,SWITCH(VALUES(Table1[Id])
,1,[AvgColumn1]
,2,[AvgColumn2]
,3,[AvgColumn3]
)
)

AvgColumn1:= AVERAGEX(
KEEPFILTERS(VALUES('viewUser1'[UId])),
CALCULATE(DISTINCTCOUNT('viewApp1'[ApplicationId]))

AvgColumn2:= AVERAGEX(
KEEPFILTERS(VALUES('viewUser2'[UId])),
CALCULATE(DISTINCTCOUNT('viewApp2'[ApplicationId]))

AvgColumn3:= AVERAGEX(
KEEPFILTERS(VALUES('viewUser3'[UId])),
CALCULATE(DISTINCTCOUNT('viewApp3'[ApplicationId]))

[Seth_C_Bauer]

@jatneerjat Direct Query is going to be the slowest possible connection. The performance issues will need to be mitigated on the database side... I would assume you need the most up to date information and that is why you are using that method. If not, I would recommend switching to Import.

If you need to stick to DQ, optimize the query, and reduce the amount of information if possible.

On top of that, you are doing row by row calcs here and those are going to be super slow.