Forum Discussion
Improving formula engine performance
Query diagnostics will help you troubleshoot your power query refresh, but not your DAX expressions. Can you post your DAX expression so we can propose optimization changes?
Regards,
Pat
Thanks mahoneypat - here's the DAX that's taking 56 seconds to run
DEFINE
VAR __DS0FilterTable =
TREATAS(
{"2020 Q1",
"2020 Q2",
"2020 Q3",
"2020 Q4"},
'bonus_region_sales_target'[remuneration_period]
)
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('bonus_applications_details'[is_eligible_for_bonus])),
NOT('bonus_applications_details'[is_eligible_for_bonus] IN {0,
BLANK()})
)
EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Application Received Date'[Application Recieved Date],
'bonus_applications_details'[application_code],
'bonus_applications_details'[fundraise_name],
'bonus_applications_details'[investor_name],
'adviser_contacts'[adviser_code],
'adviser_contacts'[adviser_name],
'current_intermediaries'[intermediary_code],
'current_intermediaries'[company_name],
'bonus_applications_details'[amount_on_application],
'bonus_applications_details'[pending_payment_amount],
'bonus_applications_details'[imputed_percentage],
'dbt_marts oisales__bonus_accelerators'[activation_type],
'bonus_applications_details'[sales_region],
'bonus_applications_details'[remuneration_period],
'bonus_applications_details'[investor_code],
'bonus_applications_details'[is_cleared],
'bonus_bdm_sales_target'[employee_name]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
"Amount", 'bonus_applications_details'[Amount]
),
[IsGrandTotalRowTotal],
0,
'Application Received Date'[Application Recieved Date],
1,
'bonus_applications_details'[application_code],
1,
'bonus_applications_details'[fundraise_name],
1,
'bonus_applications_details'[investor_name],
1,
'adviser_contacts'[adviser_code],
1,
'adviser_contacts'[adviser_name],
1,
'current_intermediaries'[intermediary_code],
1,
'current_intermediaries'[company_name],
1,
'bonus_applications_details'[amount_on_application],
1,
'bonus_applications_details'[pending_payment_amount],
1,
'bonus_applications_details'[imputed_percentage],
1,
'dbt_marts oisales__bonus_accelerators'[activation_type],
1,
'bonus_applications_details'[sales_region],
1,
'bonus_applications_details'[remuneration_period],
1,
'bonus_applications_details'[investor_code],
1,
'bonus_applications_details'[is_cleared],
1,
'bonus_bdm_sales_target'[employee_name],
1
)
ORDER BY
[IsGrandTotalRowTotal] DESC,
'Application Received Date'[Application Recieved Date],
'bonus_applications_details'[application_code]/*,
'bonus_applications_details'[fundraise_name],
'bonus_applications_details'[investor_name],
'adviser_contacts'[adviser_code],
'adviser_contacts'[adviser_name],
'current_intermediaries'[intermediary_code],
'current_intermediaries'[company_name],
'bonus_applications_details'[amount_on_application],
'bonus_applications_details'[pending_payment_amount],
'bonus_applications_details'[imputed_percentage],
'dbt_marts oisales__bonus_accelerators'[activation_type],
'bonus_applications_details'[sales_region],
'bonus_applications_details'[remuneration_period],
'bonus_applications_details'[investor_code],
'bonus_applications_details'[is_cleared],
'bonus_bdm_sales_target'[employee_name]*/
I'm stripping back the DAX to see how it's translated into SQL and can see that even if I'm only querying two columns - date and amouunt - the SQL generated is querying every single field in the fact table
I can't see a reason to query fields that aren't required. Does this indicate something wrongly configured in Power BI or Synapse?
Anonymous Perhaps we should back up. What does your source data look like and what are you trying to accomplish? I feel like we are trying to bail water when we could just plug the hole...
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Here's a go at asking the question in a better way
I'm using a composite model, with the main fact table in DirectQuery and other tables (e.g. date) in Import. Here's the model:
It has about 250k rows and about 40 columns, of which we keep about 30 and then make some measures on top.
Because it's DQ, I'm not sure extracting a sample, converting to import and sharing would replicate the same performance we see here - or would it?
The fact table contains sales with data regarding bonuses - sale id, customer info, employee info, regional info, multiple dates, amounts, bonus codes (e.g. bonus multiplier id).
The other tables are mainly date tables (we haven't implemented role playing dimensions yet) and bonus tables (multipliers and targets).
What I'm trying to solve here is to show sales ("applications") that haven't been processed ("cleared"), and to calculate the bonus multiplier for them by looking into a couple of other tables with bonus multiplier values.
E.g.
sale_id bonus period employee name customer campaign region sale amount bonus eligibility bonus multiplier bonus amount ... I've put in red the fields that are causing most problems:
- employee name - it's just a field in the source table, but taking it out of the visual drops the query time from 55 seconds to 7 seconds
- bonus multiplier (measure) - it's a couple of lookups to other tables in the data model (DAX below) but I run out of available resources while refreshing the visual
- bonus amount (measure) - very simple product of sale amount and bonus multiplier, but fails based on the multiplier
The sale amount is also a measure:
Amount = SUM(bonus_applications_details[amount])...so that I can do aggregate reporting in other tablesThis is the bonus multiplier that's causing the bigger problem:Bonus multiplier =
var default = CALCULATE(
MAX('dbt_marts sales__bonus_multipliers'[bonus_multiplier]),
FILTER(
ALL('dbt_marts sales__bonus_multipliers'[id]),
'dbt_marts sales__bonus_multipliers'[id] = SELECTEDVALUE(bonus_applications_details[bonus_multiplier_id])
)
)var override = CALCULATE(
MAX('ods_spreadsheets sales__bonus_overrides'[bonus_override]),
FILTER(
ALL('ods_spreadsheets sales__bonus_overrides'[application_code]),
'ods_spreadsheets sales__bonus_overrides'[application_code] = SELECTEDVALUE(bonus_applications_details[application_code])
)
)return
if(not(ISBLANK(override)),override,default)
(is there a reason the code snippet can't format DAX??)
This is doing a couple of lookups to get a default bonus multiplier (based on the sale period) and checking for a sale-level override (based on the sale id. These are small lookup tables, but adding this field results in the resources being exceeded.
If I add the measure, the visual says "ran out of available resources".
Maybe it's a problem in Synapse (why would the SQL be querying fields that I don't use in the visual...?)
Maybe it's a problem in the data model (but even just bringing back a field from the source table takes almost a minute...)
Maybe it's a problem in my query (but I'm using small tables and I've tried to use FILTER efficiently)
I appreciate you have lives and jobs, so honestly any tips would be hugely appreciated and if this is an unreasonable or badly worded request for help, I get it.
One quick suggestion to try is to replace your second variable
VAR __DS0FilterTable2 = FILTER( KEEPFILTERS(VALUES('bonus_applications_details'[is_eligible_for_bonus])), NOT('bonus_applications_details'[is_eligible_for_bonus] IN {0, BLANK()}) )with
VAR __DS0FilterTable2 =
Filter(Keepfilters(values('bonus_applications-details'[is_eligible_for_bonus])), 'bonus_applications-details'[is_eligible_for_bonus]>0)
Also, can you post the original measure from Power BI, not the MDX from DAX Studio? I'm not confident that will make it clearer but worth a try.
Regards,
Pat
