Powerbi Performance issues Matrix Drill downs with different tables

[Anonymous]

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?

[AnujaK]

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.

[amitchandak]

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

[Anonymous]

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.

[v-deddai1-msft]

Hi @Anonymous ,

Do both the Matter Table and the Staff table have a bi-directional  relationship to fact table?

Best Regards,

Dedmon Dai

[Anonymous]

No Bi-Directional relationships. I am going to try upgrade tabular it may be a version issue (2017 bug with matrix?) not sure if it will work.

[Anonymous]

I upgraded the tabular server to 2019 to the latest patch, the version of the tabular edition is standard. I also tested out the slow queries against the Task Manager on the analysis server. When the long query was running the server never goes over 15-20% of memory (48gbs) and the cpu never goes over 20%. Have i missed a settings in configuration?

[lbendlin]

What does DAX Studio have to say about the DAX query generated by that last drilldown?