Performance inspector for Desktop?

[rutgerboll]

Is there a way to analyze performance of a Power BI query? I have a report that is quite large and has about 80 steps of transformation. Needless to say, it takes a while to load. That's ok with me as doing it in SQL isn't possible and Excel is a definite no-go. But, that said, I'd like to optimize if possible.

[parry2k]

@rutgerboll  if you have lot of steps, best would be to go thru each step and see what steps take longer. If your data source is sql server, I guess you can use profiler to see what is taking longer to load.

if performance issue is with DAX then you can DAXStudio (available free to download) to debug the expression. I hope it is helpful.

[rutgerboll]

@parry2k i'm going through the steps now and finding that all the steps that manipulate columns with text are causing the issue. There aren't a ton of them, but there are several. Any idea how to optimze those?

[parry2k]

@rutgerboll what you are trying to do in the step you found is running slow?

[rutgerboll]

@parry2k right now the slowest step is a merge between two tables. It's joining on a 2 to 3 character unique code. One table has the code as a primary key while the other has it as a foreign key. There's about 200 rows in the primary table and 20k in the other.

[parry2k]

@rutgerboll great, you found the step. Now without knowing all the details, you can ignore merge step and instead set relationship between the table in data model in powerbi (relationship tab) and then you can use the column from primary table. 

Without knowing I'm not fully sure why you are using merge but it can be removed and relationship will be the way to go.

[rutgerboll]

@parry2k i hear what you're saying but i am not sure it is the right solution for my problem. let me explain my situation in greater detail:

I have a table with 20k rows and about 30 columns. Each row is a customer. One column holds a contract code, which relates to another table. Each other column represents the revenue transacted by that customer in a particular category of products.

I have another table that holds the contract information. For additional context, multiple customers can be on a contract - think of it like group purchasing. Each row in that table is a contract code and each column represents whether our not they are contracted in a particular category of products.

I want to join those two because I need to calculate compliance to the contract on a customer level. To put ths into context, at each customer there are several purchasers. I want to calculate the $ per purchaser and then compare that to a benchmark value that I have in another table.

Ultimatley, I have no idea how to perform all those operations in DAX because they need to be perform on a line-by-line basis

[parry2k]

@rutgerboll  I believe it can be achieved using DAX, may be best would be to send sample data (hide any sensitive inforamtion) and what you want to achieve and will try to get you the solution.

[rutgerboll]

---

@parry2k wrote:

@rutgerboll  if you have lot of steps, best would be to go thru each step and see what steps take longer.

---

Ah, i hadn't thought of stepping through each item. I'll give that a shot and report back. Thanks!