Loading time for calculated columns

[Haidarius]

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!

[HoangHugo]

Hi,

I read Microsoft documents. They have concept of "Limited Relationship", it make your RELATE can not achieve value. Try refer link below

https://docs.microsoft.com/en-us/dax/related-function-dax

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-r...

[Haidarius]

All my relationships are not limited relationships, they are from the same datasource and have 1 to * connections throughout. This was giving me values eventually but most recently stopped after a data update that increased my datasource to around 200k values but that's it, I'm certain that a powerBI report is capable of handling such values, so I'm wondering in that case how can I test the db optimality in terms of performance to BI? 

[HoangHugo]

Sure, PowerBI can handle 1 million rows in DirectQuery. Try to refer the limitation of Directquery link below, if your DirectQuery need more 1 minutes to load data, the system can be error

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#limitations-of-direct...

Also, can you share your map of relationship?