Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JoZu
Frequent Visitor

Multiple DAX Measures Construct - Optimization Suggestions needed

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 PowerBIFormatDescription
T.Transaction CurrencyTextTransaction Currency (e.g. USD, EUR, ...)
N.Net Value in Transaction CurrencyNumberValue of an Invoice in Transaction Currency
T.Order TypeTextOrder Type of an Invoice (e.g. preorder, Reorder)
N.AccountNumberID Number of the Client
N.ProductNumberID Number of the Product
D.Invoice DateDateInvoice Date 

~200'000 rows representing about 2.5 years of data. Daily increasing.

 

Sales Orders (All Status)

Column Name in PowerBIFormatDescription
N.Sales OrderTextID Number of a Sales Order
N.Sales Order ItemTextLine position ID of a sales Order
N.ProductTextID Number of a Product
N.AccountNumberID Number of a Client
N.Open Sales QuantityNumberOpen Quantity of a Sales Order (Confirmed QTY - Fulfilled QTY)
N.Open Sales Volume inTransaction CurrencyNumberValue in transaction currency of the Open Sales Qty
T.Order TypeTextDescription of the Order Type (e.g. Preorder, Reorder, ...)
N.Cancellation StatusNumberIndicator if a sales order was cancelled (4) or not (1)
N.Cancellation ReasonNumberCancellation reason ID of a sales order
D. Modification DateDateLast 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 PowerBIFormatDescription
N.Product TextID number of a product 
N.CategoryTextID 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 OrderLine ItemQuantityValueModification DateDynamic RankCancellation %Running Total of Cancellation %Cancellation Fee
112233101350001.01.202313.5%3.5%0.00
112233201200002.01.202322.0%5.5%200.00
112244102500001.02.202335.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 RegionAccount IDSummarized Cancellation Fee
A1115500.00
 2220.00
B3332500.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. 

 

 

0 REPLIES 0

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors