Slowness in calculating the DAX Measure

[Rai_BI]

Hello friends,

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

Download PBIX here

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.

[gmsamborn]

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.

[gmsamborn]

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.

[Rai_BI]

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.

[gmsamborn]

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?

[Rai_BI]

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.

[gmsamborn]

Hi @Rai_BI 

Can you post an updated pbix?

[Rai_BI]

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.

[gmsamborn]

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.

[gmsamborn]

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.

[Rai_BI]

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