Power BI

[Anton2315]

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

[lbendlin]

Use DAX Studio to evaluate the query plan and server timings for that query (ideally while connected to the semantic model in the Power BI Service).  Look out for excessive number of records - those may indicate unintentional cartesian products.  Refactor your query to apply filters/groupings earlier. ("reduce cardinality")

[Anton2315 · Accepted solution]

Hello,

Thank you for your help.

I have resolved the issue. I have made a separate table in SQL which is calculating everything because in Power BI there was no option to do it.

Best Regards.

[v-aatheeque]

Hi @Anton2315 ,

If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!