cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## Slowness in calculating the DAX Measure

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.

10 REPLIES 10
Super User

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.

Super User

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.

Helper III

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.

Super User

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?

Helper III

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.

Super User

Hi @Rai_BI

Can you post an updated pbix?

Helper III

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.

Super User

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.

Super User

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.

Helper III

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors