Forum Discussion
Powerbi Performance issues Matrix Drill downs with different tables
Hi pbi community,
I have a powerbi report which is linked to a tabular model with the schema\tables below. I am having issues in drill downs in a matrix (slow and spinning forever). I found that this happens when i have a hierachy that includes another table during the drill down.
IE Columns:
(matter table)Matter Group
(matter table)Matter Service Line 1
(matter table)Matter Service Line 2
(matter table)Matter Service Line 3
(staff table) Debtor Name
(matter Name) Matter Name
Question:
The matrix drill downs work fine, untill it try to drill down from the matter table to any other table, in this case it is the staff table, as soon as i drill down from Matter Service Line 3 to Debtor Name The performance dies.I currently have a star schema using 2 fact tables and i have checked Dax studio and confirmed i have no R1 voliations. I am using Powerbi May 2019 and Tabular 2017. We use Direct query (Have a pretty beefy server the tabular model is hosted on), and the drill down i am referencing is when you press the "+" button in the matrix.
Is this a design issue ?
Should i redesign the model so it just uses huge Fact tables rather than having seperate tables?
7 Replies
What does DAX Studio have to say about the DAX query generated by that last drilldown?
Anonymous , You diagram is not making it clear. But debtor name is in another table which 1-m from service line and measure is in a different table
Also, is Data in import mode in power BI or is in a direct query from the database? if it is a direct query from the database it can be an index issue.
My Table Structure:
1. I have a blank Table which holds all of the Measures.
2. Matter Table Links to the fact table 1-M
3. Staff table links to the Fact table 1-M
The matrix has a drill down of Matter Columns and a staff name column (from the staff table). This drill down is causing performance issues (Drilling down from a Matter column to another table). I have great server specs\ star schema with no RI errors and only bring in the data i need (Reduce Column store as much as possible).
I use direct query as the tabular server has great specs and its whole purpose is to soley run this tabular model.
Hi Anonymous ,
Do both the Matter Table and the Staff table have a bi-directional relationship to fact table?
Best Regards,
Dedmon Dai
Hi, Were you able to get this fixed? Even I am facing performance issue while drilling down in Matrix, please let me know if you have tried any work around to fix this.
