Optimising DAX consolidated by multiple FACT tables

Question

Hi all,&nbsp;&nbsp;Following my question on the other fourm (Creating Revenue Measure using 2 Fact Tables (Diff... - Microsoft Fabric Community) I've managed to create a query which works. However, following inclusion of more than 4 reporting months I get an error that the visual has exceeded the available resource.&nbsp;&nbsp;I Don't have any familarity with optimising DAX (not at this level) and was wondering how best to optimise the below code. Any help would be appriciated.&nbsp;Revenue =VAR AsatDate = &nbsp;MAX('As At Date'[Date]) + MAX('Time Table'[Time])VAR CurrentDateInContext = SELECTEDVALUE('As At Date'[Date])VAR PreviousDateInContext = CALCULATE(&nbsp; &nbsp; MAX('As At Date'[Date]),&nbsp; &nbsp; ALLSELECTED('As At Date'),&nbsp; &nbsp; KEEPFILTERS('As At Date'[Date] &lt; CurrentDateInContext))VAR test = CALCULATETABLE(SUMMARIZE(fact_opportunity_line_ledger,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fact_opportunity_line_ledger[Opportunity Line Item Id],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fact_opportunity_line_ledger[Stage Change],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fact_opportunity_line_ledger[Created Date/Time]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fact_opportunity_ledger[Created DateTime] &lt;= CurrentDateInContext)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;var LineID = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTCOLUMNS(FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Index(1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; test,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Orderby(fact_opportunity_line_ledger[Created Date/Time],DESC),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PARTITIONBY(fact_opportunity_line_ledger[Opportunity Line Item Id])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fact_opportunity_line_ledger[Stage Change] in {"Closed Won","Won"}),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Line ID",fact_opportunity_line_ledger[Opportunity Line Item Id])VAR OpID = CALCULATETABLE(VALUES(fact_opportunity_ledger[Opportunity Id]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'fact_opportunity_ledger'[Com] = "",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'fact_opportunity_ledger'[Stage] = "Closed Won",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'fact_opportunity_ledger'[Validation Stage] = "Completed",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'fact_opportunity_ledger'[Created DateTime] &gt;= PreviousDateInContext,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'fact_opportunity_ledger'[Created DateTime] &lt; CurrentDateInContext)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR TCV = CALCULATE([Total Value (Date Reporting)],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LineID,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OpID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Return TCV&nbsp;&nbsp;Thanks for any help provided.&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi Anonymous&nbsp;,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly&nbsp;&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Optimising DAX consolidated by multiple FACT tables

1 Reply

Related content

Fabric - Copilot optimisation

Validation/optimisation options for AI features

Consolidating multiple data items into fewr choices

Dax - Optimise LASTNONBLANKVALUE

Data Model - Consolidating Multiple Fact Tables