Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Rai_BI
Helper III
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.

 

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.

Rai_BI_2-1712153080428.png

 

 

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

Rai_BI_3-1712153139899.png

 

 

 

10 REPLIES 10
gmsamborn
Super User
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.

gmsamborn
Super User
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.

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.

 

Rai_BI_0-1712176044140.png

 

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?

 

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.

Hi @Rai_BI 

 

Can you post an updated pbix?

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

Rai_BI_0-1712252047896.png

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
Super User
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.

gmsamborn
Super User
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:

gmsamborn_0-1712162433988.png

 

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.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors