Performance issues with measures on live connection

PBI_alma — Fri, 23 Sep 2022 15:13:05 GMT

Hello community,

I am new to PBI on Analysis service respectively live connection and hope you can help me.

I am working on a data model with about 70 tables linked with various active and inactive relationships in average about 5 to 10 for each table, most of them inactive. The largest of the tables has about 15 million rows.

Question 1: As soon as I use a Measure, the performance goes down. Especially in measures where I enable or disable relationships via USERELATIONSHIP() or CROSSFILTER(). This is also the case when I limit the time period in a slicer to a few days before adding the measure to a visual. Online I have found only the use of VAR as a performance tip, but even that has not brought any improvement. Do you have any tips here?

Example of a measure which loads long, would be this quite easy one:

Rate =
var number = calculate(distinctcount(ID),
column 1 = true,
column 2 = true)

var number_2 = distinctcount (ID)

return number / number_2

Question 2: Is there any way to activate or deactivate relationships within PBI other than within DAX measures?

Question 3: How do you handle larger data models and their complex relationships? What experiences have you had? How do you compensate the absence of the "query editor" in measures and at the same time ensure good performance? Do you have the same problems?

I am looking forward to your insights,

best regards

PBI_alma

Re: Performance issues with measures on live connection

daXtreme — Sat, 24 Sep 2022 07:56:51 GMT

"Do you have any tips here?"

Yeah, actually I do 🙂 With such a big model and complex relationships... please consult somebody that professionally deals with such complexities on a daily basis. You can consult Marco Russo and/or Alberto Ferrari. Of course, you'll have to pay for this service but these guys have worked for the biggest companies in the world helping them set up tabular cubes as big as 120GB in memory (you can't even imagine the complexity of such a cube, trust me). I know about it since I've worked for one such a company in the past and without their help even very skilled and smart people would have been lost in the maze of relationships and optimization techniques.

This is by far the best advice you can get.

topic Re: Performance issues with measures on live connection in DAX Commands and Tips

Performance issues with measures on live connection

Re: Performance issues with measures on live connection