Power BI

Hello Guys,

I have a client for whom I'm optimizing their report.

This is the model :

My largest tables are inv_transaction_lines_by_day_store_document with 17 million rows and crm_sales_order_lines_store_day_product with 11 million rows. The clients want a matrix in which they can have the products and all dates within selected periods. I have created a DAX expression that works perfectly when visualizing only the dates or only the products, but when I try to combine both in one visual, it gives me an error saying that I don't have enough resources.

Everything is in Fabric Capacity F4, which is why I'm using DirectQuery mode, as I am connected to the semantic model.

Please help me fix the errors and improve the expression.

VAR MaxDate = MAX('Calendar'[Date])

VAR Store_Counter =

CALCULATE(

DISTINCTCOUNT(Sharepoint_Location_Data[LocationWithID]),

FILTER(

CALCULATETABLE(

VALUES(Sharepoint_Location_Data[Opening_Date]),

Sharepoint_Location_Data[Opening_Date] <= MaxDate

),

VAR StockQuantity =

CALCULATE(

SUM(inv_transaction_lines_by_day_store_document[Quantity_by_Movement_Type]),

'Calendar'[Date] <= MaxDate

)

RETURN StockQuantity > 0

)

)

VAR Store_Counter_By_Sales =

CALCULATE(

DISTINCTCOUNT(crm_sales_order_lines_store_day_product[Line_Store_Id]),

FILTER(

CALCULATETABLE(

VALUES(Sharepoint_Location_Data[LocationWithID])  -- Важно: премахваме ненужните колони от `VALUES`

),

VAR StockQuantity =

CALCULATE(

SUM(inv_transaction_lines_by_day_store_document[Quantity_by_Movement_Type]),

'Calendar'[Date] <= MaxDate

)

RETURN StockQuantity <= 0

)

)

RETURN Store_Counter + Store_Counter_By_Sales

Thank you in advance