Help with improving the performance

DAX DAX Calculations
1

Hello everyone,

Can anyone help in optimizing the DAX or provide me with an option to improve the performance ?
I am using Direct Query method to get the underlying data set.

SQL : select Store, Score, Rank, Region, State from score;

I have attached the PBI file with sample dataset.
PBI_DAX_Optimisation.pbix (32.9 KB)

Store Score Rank Region State
ABC 56 3 A1 S1
BCD 75 2 A1 S1
EFG 89 2 A1 S1
GHI 20 3 A2 S1
LMN 10 3 A2 S1
STU 99 1 A2 S1
QVW 91 1 A3 S3
JJJ 60 2 A3 S3
DDI 100 1 A3 S3
MJM 59 3 A3 S3

I have to group the store based on the ranking and number of stores at State and Region level.

I am using the is measure to get

Rank1 =
var rowcount = COUNTROWS(FILTER(Score,Score[Rank]==1))
Return
If(rowcount = Blank(),0,rowcount)

On the real dataset, it takes nearly 20seconds to load Ranking table visual.
Any ideas on improving the performance of the visual?

Thanks,
Stella

2

Hi @stellaj,

Can you please test a function like the one below and tell me if performances are better ?

Rank1 bis =
IF( SELECTEDVALUE( Score[Rank] ) = 1, 1, 0 )

I think it could be better because you remove the table function.
It’s difficult for me to test it, because it is really fast on my computer…

I hope it will help you :slight_smile:

Best regards,
JBocher

3

Thanks for reply JBocher.
I tried the function, it is not working at Region/State level

Rank1 bis =
IF( SELECTEDVALUE( Score[Rank] ) = 1, 1, 0 )

And add the measure at “Region level”
Region,Rank1,Rank2,Rank3,No.of Stores,Rank1 bis
A1,0,2,1,3,0
A2,1,0,2,3,0
A3,2,1,1,4,0

Not sure, what I am doing wrong.

Any suggestions ?

4

@stellaj How many rows are there in the table?

5

1000 rows

6

@stellaj 1000 rows in real dataset and you are using DirectQuery? Also 1000 rows are not enough to make that trivial code slow. Something is missing here…

1 Like
7

@AntrikshSharma the report uses Direct Query method to source the data from Snowflake. And we are seeing very bad performance on all the visuals. This ranking visual takes 35sec to load (ranking and number of stores at State and Region level)

8

Bumping this post for more visibility.

Hello @stellaj while waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!

9

Hi @Stellaj! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!