cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors