topic Re: Slowness in calculating the DAX Measure in DAX Commands and Tips

Slowness in calculating the DAX Measure

Rai_BI — Wed, 03 Apr 2024 14:13:31 GMT

Hello friends,

I need help improving the performance of a DAX measure I created, as it is exceeding the available resources.

Each salesperson has goals and my goal is to break down the salespeople's goals for their respective customers.

First I need to calculate the percentage weight (%) of the customer in the seller's portfolio and multiply this percentage by the vendor's target, so I will have the client's target.

The problem is that the calculation is very heavy and is not carrying the visuals. In other words, I am unable to complete this task.

I need help optimizing the DAX measurement I made so that it loads the information in the visuals quickly.

Below is the step by step of what I did:

DAX Measures

| Goals = SUM( f_goals[Goal] )
-------------------------------------------------- ------
| Sales Avarege =
VAR Sum =
CALCULATE(
[| Sales US$],
CALCULATETABLE(
DATESINPERIOD('dCalendar'[Date],
MAX('dCalendar'[Date]),
-12,
MONTH),
'dCalendar'[Month to Current Month] = 1
)
)

RETURN
IF(
ENDOFMONTH(
'dCalendar'[Date]),
Sum/12
)

-------------------------------------------------- ------

| Goal by Customer =

VAR _CustomerSales = [| Sales Avarege]

VAR _TotalSales =
CALCULATE(
[| Sales Avarege],
ALL('d_customer table'),
VALUES('d_Sellers (key)'[Seller ID])
)

VAR _PercentToTotal =
DIVIDE(_CustomerSales, _TotalSales)
VAR _PortfolioTarget = [| Goals]
VAR _CustomerTarget = _PercentToTotal * _PortfolioTarget
RETURN
_CustomerTarget

See below the 'Goal by Customer' measure which is working well when I don't add the customer in the visual.

However, when I try to add customer information, Power BI cannot calculate it due to the calculation being too heavy.

Re: Slowness in calculating the DAX Measure

gmsamborn — Wed, 03 Apr 2024 16:47:25 GMT

Hi Rai_BI

I took a look at your model and noticed a couple of many-to-many relationships. It turns out that they are the culprits.

I created a couple of extra dimension tables: d_categories and d_sellers (the oridinal d_sellers is now Sellers_Customers). By making changes to the relationships, I came up with a model that looks like this:

I did this using DAX. In practice, modelling changes like this should be done in Power Query (or the source). Since I couldn't make changes in PQ, I had to do it in DAX.

I had to change the visuals to use the seller columns from the new d_seller dimension.

Your measures will probably need a few changes.

Please try to avoid many-to-many relationships whenever possible.

Let me know if you have any questions.

Re: Slowness in calculating the DAX Measure

Rai_BI — Wed, 03 Apr 2024 17:22:16 GMT

Thank you very much! Can you explain why using the many-to-many relationship in this case makes the model slow?

Re: Slowness in calculating the DAX Measure

gmsamborn — Wed, 03 Apr 2024 17:39:17 GMT

Hi Rai_BI

I think the biggest problem is how you relate 'd_customer_table' with 'd_sellers (key)'.

Your current structure of 'd_sellers (key)' actually looks like a bridge table. By creating an actual dimension table for sellers and keeping the bridge table MIGHT make a difference.

I would try to eliminate the other many-to-many relationships if possible.

I'll look at changing the biggest problem (IMO) at get back to you.

Re: Slowness in calculating the DAX Measure

gmsamborn — Wed, 03 Apr 2024 22:21:54 GMT

Hi Rai_BI

I split 'd_sellers (Key)' into 'Sellers_Customers' and d_sellers'. After changing the relationships, it seems to be closer to what you want.

I would still look at how having Category in d_products forces that other many-to-many.

Re: Slowness in calculating the DAX Measure

Rai_BI — Wed, 03 Apr 2024 20:27:39 GMT

Performance improved a lot in the desktop version, however when publishing to the power bi service the visuals do not load as they exceed the available resources.

Re: Slowness in calculating the DAX Measure

gmsamborn — Wed, 03 Apr 2024 21:06:48 GMT

HI Rai_BI

Were you able to create the new dimension table(s) and get rid of the many-to-many relationships? Were you able to do it in Power Query?

My version (done in DAX) was for demonstration purposes only. Power Query would be MUCH better.

How many rows in your production f_sales table?

What is your datasource?

Re: Slowness in calculating the DAX Measure

gmsamborn — Wed, 03 Apr 2024 22:58:51 GMT

Hi Rai_BI

I came up with the Power Query steps that you'll need.

- duplicate 'd_products' and rename 'd_categories'
- select [Product Category ID] and [Product Category] and remove other columns
- remove duplicates

- remove [Product Category] from 'd_products'

- duplicate 'd_Sellers (key)' and rename 'Sellers_Customers'
- in 'Sellers_Customers', remove [Customer Name] and [Name Seller]
- in 'd_sellers', remove [Customer ID] and [Customer Name]

Afterwards, you'll need to remove the many-to-many relationships and set up relationships for the new dimension tables.

Let me know if you have any questions.

Re: Slowness in calculating the DAX Measure

Rai_BI — Thu, 04 Apr 2024 12:04:54 GMT

Yes, I did everything you said. I did it directly from the source using SQL. My real database is very similar to the example PBIX that I made available.

My real customer table has more than 10 thousand rows and my product table has more than 50 thousand rows.

The sales table has millions of rows as it has data from the last 5 years.

Re: Slowness in calculating the DAX Measure

gmsamborn — Thu, 04 Apr 2024 13:25:18 GMT

Hi Rai_BI

Can you post an updated pbix?

Re: Slowness in calculating the DAX Measure

Rai_BI — Thu, 04 Apr 2024 17:35:23 GMT

Of course, I updated the same pbix file as before and you can download it from the same link as above. Download PBIX here

I just made a small change in that the filter direction I kept as "both", as it is important to count how many customers there are in each seller's portfolio.