DrillThrough in Direct query

[Anonymous]

Hello all, 

I face an issue on a report that contains a DrillThrough functionality...

Let me explain: 

My first tab in the report is a summary page with few slicers and a little table containing 5 rows and 4 columns.

I put a Drillthrough functionality on the value to see another page containing detail data.

The detail data tab contains a big table with around 70 columns and, depending on the value choosen to drill, between few hundred and few thousand rows.

For info, the summary tab and the detail tab are based on the same fact table, which is in direct query.

In my model, I have 20 dimensions, which are in import.

My fact table currently contains 10M rows and is supposed to grow up.

My issue:

- When it works, the display of the detail table is so long (around 1 minute)

- Since 2 days, it didn't work anymore:

I checked the direct query with the "performance analyzer" and I see a huge query with a lot of rows (select top 1000001, etc...)

In my fact table, I deleted all the fields that are useless for this report.

On the source side, which is an Azure SQL Database, all the necessary Index and partitioning have been made.

So I'm a litlle lost with that...

Do I make the thing the wrong way? I do not see any other way to do that...

Thank you in advance for your help.

[v-chenwuz-msft]

Hi @Anonymous ,

This situation can occur with a simple chart that includes a very high cardinality column, with the aggregation option set to Don’t Summarize. Please try to add more filters when drill through. The visual needs to only have columns with a cardinality below 1 million.

For more details you can refer this  link.

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

If it does not work please create a support ticket.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.