Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
IzuruWi
Frequent Visitor

Improve the data loading performance of table visual

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.

6 REPLIES 6
AbbasG
Memorable Member
Memorable Member

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

AbbasG
Memorable Member
Memorable Member

@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.

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

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

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
        )

@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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors