Hello community,
I have a report connected to SQL Server via DirectQuery. After loading around 15 tables (ranging from 20k to 150k rows each, and some even being 15 rows), creating connections and creating multiple visualizations, the report started taking a lot of time to load my matrices.
I have 3 matrices, in each I am showing 3 values and only 2 rows with some filters on the page of the report. Of these 3 values in each, we have a total of 9 where the first 3 are directly input from the tables as a field and the rest of the 6 depend on these 3 original fields but go through some filters and a simple multiplication equation.
Lets assume we have the following tables:
1- Acres (main table which I am creating these calculated columns in)
2- Units
3- References
Our relationships are as follows, Acres and units share a relationhsip, references and units have a relationship
I created a column in units that copies basically one of the columns in references using RELATED(), from which I created the calculated columns based on the value of that copied column which is also RELATED() in Acres, with an IF() condition.
Calculated_column_in_acres = IF(
RELATED(Units[Copied_Column]) = "VALUE_A", Acres''[column1], BLANK())
where column1 is = 'Acres'[columnA]*'Acres'[columnB]
I have laid out the exact details of how the report is being fed data, to the operations and the complexity level. I am unsure why the matrices (tables) just keep loading and not giving out any values, the only table thats loading after a minute or 2 is the one that directly has input from the directQuery. My equations are correct as it was showing the values last week before I had a database update. In the case of the issue being storage or powerBI limitations, what do you recommend I do??
PS: oh and the same data for these tables is plotted easily on column charts..
Thank you!
