Improve the data loading performance of table visual

[IzuruWi]

Hi all.

I have started preparing a Power BI report to visualize information using table visual with drill down information option in a secondary tab in the same report. I want to increase the speed of the visualization as much as possible based on the customer filtrations.

This report has 4 DIM tables which has maximum up to 6 columns and around 10K rows in each table. Also, there's 1 fact table which contains around 1.8Milion rows with 8 columns.

This report is using direct query option to load data from oracle database. I have used SQL query to exact required columns to minimize the data modelling in the tables.  And I have made column values which need conditional formatting using "Case" function in SQL query prior to the data load. In addition to that I have disabled the auto date/time option from the global settings as well. All these were made significant improvement in report refreshing.

But even after all this, I don't see much improvement in data visualization loading. Please guide me to find the cause and solve this issue. Much appreciated community support.

Thanks.

[AbbasG]

@IzuruWi I would love to help on this, but to be honest I am not the DAX master yet! Sorry.

[AbbasG]

@IzuruWi Have you checked visuals/query performance through Performance analyzer or DAX studio?? It can give you a fair idea about the visual performance that takes most time to load.

[IzuruWi]

@AbbasG I have checked those and found it was were OK. But still the issue is same..

[AbbasG]

@IzuruWi can you share sample data or pbix after removing the sensitive information?

[IzuruWi]

Hi @AbbasG  I found this dax causes some sort of a performance issue for the visualization. Can you please guide me to improve this dax?

SWITCH (
            TRUE (),
            CONTAINS (
                DIM_ANALYTIC_STRUCT_ACCOUNT,
                DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)], "1 - CURRENT ASSETS"
            ), SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),
            CONTAINS (
                DIM_ANALYTIC_STRUCT_ACCOUNT,
                DIM_ANALYTIC_STRUCT_ACCOUNT[STRUCTURE_NODE (groups)], "2 - NON - CURRENT ASSETS"
            ), SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ),
            SUM ( FACT_CONSOL_BALANCE_OL[BALANCE] ) * -1
        )

[IzuruWi]

@AbbasG This executes on direct query connection. So, there won't be any data in the pbix. Couldn't change it import mode, because of after few sets of records it'll stops import and gives an error