Forum Discussion
Optimizing a Calculated Column that Uses Filters
Hi, I have a calculated column that references 3 tables that performs EXTREMELY slowly. It's goal is to get the distinct order count when the orders are for items that relate to the kit column via a mapping table that maps specific items to those kits. The result is a metric that shows how many orders occurred for that specific kit type. So for example if one order had 2 items on it that were each mapped to 1 kit, I would get only one count for that. For example:
Kit Table
| Kit | Customer | Month | Calculated Column (Orders) | |||
| A | Microsoft | 202301 | 2 | |||
| A | 202301 | 0 | ||||
| B | Microsoft | 202301 | 2 | |||
| B | 202301 | 0 | ||||
| C | Microsoft | 202301 | 1 | |||
| C | 202301 | 0 | ||||
| D | Microsoft | 202301 | 1 | |||
| D | 202301 | 0 | ||||
| E | Microsoft | 202301 | 1 | |||
| E | 202301 | 0 |
Sales
| Order # | Customer | Month | Item # | |||
| 12345 | Microsoft | 202301 | 000001 | |||
| 12345 | Microsoft | 202301 | 000002 | |||
| 12345 | Microsoft | 202301 | 000003 | |||
| 54321 | Microsoft | 202301 | 000001 | |||
| 54321 | Microsoft | 202301 | 000002 | |||
| 54321 | Microsoft | 202301 | 000003 | |||
| 54321 | Microsoft | 202301 | 000004 | |||
| 54321 | Microsoft | 202301 | 000005 | |||
| 67890 | 202301 | 000003 |
Mapping Table
| Item # | Kit | |
| 000001 | A | |
| 000001 | D | |
| 000001 | E | |
| 000002 | A | |
| 000003 | B | |
| 000004 | B | |
| 000005 | C |
The way to read the logic of the calculated column in words is: How many orders did Microsoft place in January of 2023 for items that appear in Kit A? We check the Mapping table and find that Kit A has items 000001 and 000002 so then we check the order table for those items and find that 2 separate orders were placed, therefore the result is 2. The next row asks the same question of Google, and we find that those items do not appear in any orders for Google in that month.
My DAX is this:
Orders = CALCULATE( DISTINCTCOUNT( Sales[Order #] ),FILTER( Sales, //Get Distinct Order Count
( Sales[Month] = 'Kit Table'[Month] )* //Filter sales by month
( Sales[Customer] = 'Kit Table'[Customer] )* //Filter sales by customer
( Sales[Item #] IN (SUMMARIZE(FILTER(ALL('Mapping Table'),'Mapping Table'[Kit] = 'Kit Table'[Kit]),'Mapping Table'[Item #])) ) //Filter only sales that have items that are related to the current kit using the mapping table as a reference
))
My main issue is that the "Kit Table" is around 60K rows, the "Mapping Table" some 30K rows, and the "Sales" table is some 1M rows. The only way I have been able to optimize so far is to pull a smaller sales history which takes it from a 30 min to around a 5-10 minute load (still not idea). I will also note that removing the MONTH filter from the above DAX greatly increases the speed as well back to normal calculation times (nearly instant). I am open to restructuring the Mapping table if I have to in order to optimize but the Kit and Sales tables come from fixed sources. I can add calculated columns as I am doing here but cannot restructure. The challenge that has made this complicated is the fact that Kit orders can apply to so many items that I need to basically cross reference two tables for each line of my Kit table.
Any help is much appreciated.
One approach would be to create a bridge table between Kit and Mapping. You can create this table of distinct Kits in Power Query or DAX. This enables you to use the RELATEDTABLE function that should perform better.
Calculated column in Kit table:
Order Count = VAR vCustomer = Kit[Customer] VAR vMonth = Kit[Month] VAR vItems = TREATAS ( SELECTCOLUMNS ( RELATEDTABLE ( Mapping ), "Item #", Mapping[Item #] ), Sales[Item #] ) VAR vResult = CALCULATE ( DISTINCTCOUNT ( Sales[Order #] ), Sales[Customer] = vCustomer, Sales[Month] = vMonth, vItems ) RETURN IF ( ISBLANK ( vResult ), 0, vResult )
3 Replies
One approach would be to create a bridge table between Kit and Mapping. You can create this table of distinct Kits in Power Query or DAX. This enables you to use the RELATEDTABLE function that should perform better.
Calculated column in Kit table:
Order Count = VAR vCustomer = Kit[Customer] VAR vMonth = Kit[Month] VAR vItems = TREATAS ( SELECTCOLUMNS ( RELATEDTABLE ( Mapping ), "Item #", Mapping[Item #] ), Sales[Item #] ) VAR vResult = CALCULATE ( DISTINCTCOUNT ( Sales[Order #] ), Sales[Customer] = vCustomer, Sales[Month] = vMonth, vItems ) RETURN IF ( ISBLANK ( vResult ), 0, vResult )DataInsights thank you so much! This is approximately 4-5x faster. It still has to "think" but I think this very likely solves any timeout issues I would have when I publish this report. I only partially understand how this TREATAS function is working so I'll need to wrap my head around it at some point, but your DAX gave the exact same values as my slower column so it is working perfectly. I am assuming TREATAS utilizing relatedtable essentially is just a more efficient way to intersect those two tables.
Glad to hear that performs better. The TREATAS function changes the lineage of a column so that it can be used as a filter in another table. Essentially, treat Mapping[Item #] as Sales[Item #].
