Good afternoon dear Power BI community
I seek for your support and suggestions. I have a functional DAX measure construct of various measures delivering results and fulfilling business requirements. However, the performance is very very bad. I am uncertain where and what exactly I am doing wrong - and what potential solutions are.
General business requirements
Our Business is introducing a cancellation fee for sales orders. Therefore, this needs to be tracked in our Power BI Sales Reports which are accessed by our Sales Reps. For this case a detailled list of all cancelled orders, order positions and applied cancellation fee shall be displayed on a separate report page. Furthermore, we also want to monitor it on a higher level: list per sales region, all customers and their total cancellation fee.
Note: We do not charge cancellation fee once a threshold of 5% compared to total orders is reached. The cancellaton fee is 10% of a cancelled sales order position, but its limited to 300 per piece (whatever currency the sales order is).
Data Model and Size of Tables
For this case the following tables are relevant:
Invoice Volume
| Column Name in PowerBI | Format | Description |
| T.Transaction Currency | Text | Transaction Currency (e.g. USD, EUR, ...) |
| N.Net Value in Transaction Currency | Number | Value of an Invoice in Transaction Currency |
| T.Order Type | Text | Order Type of an Invoice (e.g. preorder, Reorder) |
| N.Account | Number | ID Number of the Client |
| N.Product | Number | ID Number of the Product |
| D.Invoice Date | Date | Invoice Date |
~200'000 rows representing about 2.5 years of data. Daily increasing.
Sales Orders (All Status)
| Column Name in PowerBI | Format | Description |
| N.Sales Order | Text | ID Number of a Sales Order |
| N.Sales Order Item | Text | Line position ID of a sales Order |
| N.Product | Text | ID Number of a Product |
| N.Account | Number | ID Number of a Client |
| N.Open Sales Quantity | Number | Open Quantity of a Sales Order (Confirmed QTY - Fulfilled QTY) |
| N.Open Sales Volume inTransaction Currency | Number | Value in transaction currency of the Open Sales Qty |
| T.Order Type | Text | Description of the Order Type (e.g. Preorder, Reorder, ...) |
| N.Cancellation Status | Number | Indicator if a sales order was cancelled (4) or not (1) |
| N.Cancellation Reason | Number | Cancellation reason ID of a sales order |
| D. Modification Date | Date | Last Date when a Sales order position was modified in our ERP system |
~275'000 rows representing about 2.5 years of data. Daily increasing.
Product Master
| Column Name in PowerBI | Format | Description |
| N.Product | Text | ID number of a product |
| N.Category | Text | ID Number of the category (3 digit code, e.g. 101 or 106) |
8'700 rows with distinct product ID's
Sales Order as well as Invocie Volume tables have active 1:n relations with Product Master (dim) using N.Product column.
Sales Order as well as Invocie Volume tables have active 1:n relations with Accounts All (dim) using N.Account column. 2'700 distinct account ID's.
There are various other tables - such as sales budget, AR data, purchases orders and budget - in the data model but not relevant for this use case.
Measures
As mentioned in the title, I came up with a construct of multiple measures:
The Cancellation % is calculated with this first measure. "Baseline" to compare to is: total Turnover since date X + all open sales orders matching our criteria + all orders that were already cancelled. The second cancelled order variable is respecting row context, this is why i left out the allexcept() function. Resulting % using divide function.
M.TEST.NEW Cancellation % =
VAR _Turnover =
CALCULATE(
SUMX('Invoice Volume', 'Invoice Volume'[N.Net Value in Transaction Currency]),
'Invoice Volume'[T.Order Type] IN {"Preorder", "Vororder"},
'Invoice Volume'[D.Invoice Date] >= DATE(2022, 07, 01),
TREATAS(ALLSELECTED('Sales Order (All Status)'[N.Account]), 'Invoice Volume'[N.Account]),
'Product Master'[N.Category] IN {"101", "102", "103", "104", "105", "106"}
)
VAR _OpenOrders =
CALCULATE(
SUMX('Sales Order (All Status)', 'Sales Order (All Status)'[N.Open Sales Volume Transaction Currency]),
ALLEXCEPT('Sales Order (All Status)', 'Sales Order (All Status)'[N.Account]),
'Sales Order (All Status)'[N.Account] = SELECTEDVALUE('Sales Order (All Status)'[N.Account]),
'Sales Order (All Status)'[N.Cancellation Status] = "1",
'Sales Order (All Status)'[T.Order Type] IN {"Preorder", "Vororder"},
'Product Master'[N.Category] IN {"101", "102", "103", "104", "105", "106"}
)
VAR _AllCancelledOrders =
CALCULATE(
SUMX('Sales Order (All Status)', 'Sales Order (All Status)'[N.Open Sales Volume Transaction Currency]),
ALLEXCEPT('Sales Order (All Status)', 'Sales Order (All Status)'[N.Account]),
'Sales Order (All Status)'[N.Account] = SELECTEDVALUE('Sales Order (All Status)'[N.Account]),
'Sales Order (All Status)'[N.Cancellation Status] = "4",
'Sales Order (All Status)'[T.Order Type] IN {"Preorder", "Vororder"},
'Sales Order (All Status)'[N.Cancellation Reason] IN { "Z02", "Z05" },
'Sales Order (All Status)'[D.Modification Date] >= DATE ( 2022, 07, 01 ),
'Product Master'[N.Category] IN {"101", "102", "103", "104", "105", "106"}
)
VAR _CancelledOrder =
CALCULATE(
SUMX('Sales Order (All Status)', 'Sales Order (All Status)'[N.Open Sales Volume Transaction Currency]),
'Sales Order (All Status)'[N.Cancellation Status] = "4",
'Sales Order (All Status)'[T.Order Type] IN {"Preorder", "Vororder"},
'Sales Order (All Status)'[N.Cancellation Reason] IN { "Z02", "Z05" },
'Sales Order (All Status)'[D.Modification Date] >= DATE ( 2022, 07, 01 ),
'Product Master'[N.Category] IN {"101", "102", "103", "104", "105", "106"}
)
VAR _TotalPreOrders = _Turnover + _OpenOrders + _AllCancelledOrders
RETURN
DIVIDE(_CancelledOrder, _TotalPreOrders)
Next up we have what I call the "dynamic ranking". Basically an index, making sure every individual position is treated individually when it comes to running totals. I want it to order first by modification date, then sales order ID, then line item ID (limited to 1000). As there are a lot of cases where dozens of lines are cancelled at the same time I cannot simply refer to the modification date. Unfortunately there is no ID in the raw data, which we could use.
M.TEST.Dynamic Rank =
VAR _SalesOrder = VALUE(SELECTEDVALUE('Sales Order (All Status)'[N.Sales Order]))
VAR _LineItem = VALUE(SELECTEDVALUE('Sales Order (All Status)'[N.Sales Order Item])) / 1000
VAR _Date = CONVERT(SELECTEDVALUE('Sales Order (All Status)'[D.Modification Date]), INTEGER)
VAR _MaxSalesOrder = CALCULATE(MAX('Sales Order (All Status)'[N.Sales Order]), REMOVEFILTERS()) +1000
VAR _RankingIndex = DIVIDE(_SalesOrder + _LineItem, _MaxSalesOrder) + _Date
VAR _LookupTable =
ADDCOLUMNS(
ALLSELECTED('Sales Order (All Status)'),
"@DateSalesOrderSum",
DIVIDE(VALUE('Sales Order (All Status)'[N.Sales Order]) + (VALUE('Sales Order (All Status)'[N.Sales Order Item]) / 1000), _MaxSalesOrder) + CONVERT('Sales Order (All Status)'[D.Modification Date], INTEGER)
)
VAR _CurrentValue = DIVIDE(_SalesOrder + _LineItem, _MaxSalesOrder) + _Date
VAR _ResultingRank =
RANKX(
_LookupTable, [@DateSalesOrderSum], _CurrentValue, ASC)
RETURN
IF(
HASONEVALUE('Sales Order (All Status)'[N.Sales Order]) && HASONEVALUE('Sales Order (All Status)'[N.Sales Order Item]) && HASONEVALUE('Sales Order (All Status)'[D.Modification Date]), _ResultingRank)
Running total of cancellation % using the previously shown dynamic ranking
M.TEST.NEW Running Total of Cancellation % =
VAR CurrentRank = [M.TEST.Dynamic Rank]
RETURN
SUMX(
FILTER(
ALLSELECTED('Sales Order (All Status)'),
[M.TEST.Dynamic Rank] <= CurrentRank
),
[M.TEST.NEW Cancellation %]
)
Cancellation Fee calculation: 10% of sales order Value, max 300 per quantity cancelled. Once the threshold of 5% of our running total cancellation % is reached it starts charging the fee. However, there might be "non-relevant" cancellations later on, thats why there is this AND() condition checking for running total as well as cancellation % on every line.
M.TEST.NEW Cancellation Fee =
VAR _SalesVolume = SUM('Sales Order (All Status)'[N.Open Sales Volume Transaction Currency])
VAR _SalesQTY = SUM('Sales Order (All Status)'[N.Open Sales Quantity])
VAR _RunningTotal = [M.TEST.NEW Running Total of Cancellation %]
VAR _Table =
CROSSJOIN(DISTINCT('Sales Order (All Status)'[N.Account]), DISTINCT('Sales Order (All Status)'[N.Sales Order]), DISTINCT('Sales Order (All Status)'[N.Sales Order Item]))
RETURN
SUMX(
'Sales Order (All Status)',
IF(
AND(
[M.TEST.NEW Cancellation %] > 0,
_RunningTotal > 0.05
),
MIN(
0.1 * _SalesVolume,
300 * _SalesQTY
)
)
)
For the purpose of reporting a total cancellation fee per account without row context in a visual, I created this measure (I think thats what we call a virtual table to simulate row context, right?).
M.TEST.Summarized Cancellation Fee =
VAR _account = SELECTEDVALUE('Sales Order (All Status)'[N.Account])
VAR _filtered_table =
CALCULATETABLE(
'Sales Order (All Status)',
'Sales Order (All Status)'[N.Account] = _account
)
VAR _summary_table =
SUMMARIZE(
_filtered_table,
'Sales Order (All Status)'[D.Modification Date],
'Sales Order (All Status)'[N.Sales Order],
'Sales Order (All Status)'[N.Sales Order Item],
--"Rank", [M.TEST.Dynamic Rank],
--"Cancellation Total", [M.TEST.Running Total with dynamic Ranking],
"Cancellation Fee", [M.TEST.Cancellation Fee]
)
RETURN
SUMX(_summary_table, [Cancellation Fee])
Visuals and Performance
To meet the first requirement (details per position; explaining total cancellation fee) I have created a table visual looking like this:
| Sales Order | Line Item | Quantity | Value | Modification Date | Dynamic Rank | Cancellation % | Running Total of Cancellation % | Cancellation Fee |
| 112233 | 10 | 1 | 3500 | 01.01.2023 | 1 | 3.5% | 3.5% | 0.00 |
| 112233 | 20 | 1 | 2000 | 02.01.2023 | 2 | 2.0% | 5.5% | 200.00 |
| 112244 | 10 | 2 | 5000 | 01.02.2023 | 3 | 5.0% | 10.5% | 500.00 |
I have accounts with aroung 50 positions. In this case calculation ranges somewhere areound 6 seconds. I have seen accounts where i took up to 10 seconds, which is not useable at all.
To meet the second requirement (overiew over market regions and their accounts with total cancellation fee each) I have created a matrix visual looking like this:
| Sales Region | Account ID | Summarized Cancellation Fee |
| A | 111 | 5500.00 |
| | 222 | 0.00 |
| B | 333 | 2500.00 |
When running it for all our markets it took aroung 60 seconds to compute. I wondered that I didnt encounter a runtime error. Again, this takes way too long and wont be displayed in the Power BI service.
Solution attempts and thoughts
I was playing around with CALCULATE() and FILTER() and tried optimizing measures with either one of those. But performance seemed to stay about the same. General goal was to limit the number of rows that need to be analyzed. e.g. limiting to sales order of the respective account (see for example my VAR _filtered_table in summarized cancellation fee measure). But still I did not see any great increase in performance.
I found out that when combining data from two tables calculated lines might increase incredibly fast. Thats when I watched and tried to apply knowledge from Alberto Ferrari (sqlbi.com - Optimizing nested iterators in DAX - YouTube).
An idea I had, was that maybe the ranking measure could be replaced by a calculated column already in power query and used as an ID in the Sales Order (all Status) table. However, when analyzing performance I did not really see a big impact when using or replacing this measure.
As there are a bunch of (for this usecase) unused tables in the data model, I was asking myself if this has an influence? I believe that this is not a problem, right? Are unused tables affecting overall performance?
I hope to have covered all the relevant topics and documented the information needed to have a fruitful discussion. I am very greatful if some of you genious minds here, could help me out and find solutions to increase performance of my measures to be able to publish it to our live reports.
