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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.