CODEWITHSUNDEEP
powerbi
Randy Minder
Randy Minder

Reputation: 48402

Power BI Aggregations - Detail Tables Must Be DirectQuery Tables?

I have a simple data model, from the Contoso database, that looks like this:

enter image description here

I'm trying to set up the table named Online Sales Aggregate as an aggregate table. When I attempt to set up a mapping, all the detail tables are disabled (see below) enter image description here

When I hover over a table I see a message that says, "Customers (for example) must be a DirectQuery table to be used as the detail table."

All the tables in the model, including the Online Sales Aggregate table were imported. Why do the detail tables need to be DQ tables?

Upvotes: 1

Views: 649

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

This is currently a limitation that Microsoft has imposed at least while aggregates are still in preview.

From Microsoft's documentation:

Detail table must be DirectQuery, not Import.

According to Microsoft people, it's likely that this limitation will eventually go away.

v-lili6-msft: Power bi product team is improving this preview feature

JoshCaplan-MSFT: This is still a work in progress but it is coming.

To expand on what David says below, I'd guess that removing this limitation is not a high priority since the main use case for aggregations is for datasets that are too unwieldy to import. If you've already imported all the data, then adding an aggregate table probably won't really speed things up that much in most cases.

If you still do need an aggregate table for an imported table, then you can do the workaround he describes by creating a summarized table via the query editor or a DAX calculated table and write your measure(s) to try to read from that first. An added bonus with this method is that you can use custom measures in your summarized table instead of being limited to aggregate summarization functions (Count, GroupBy, Max, Min, Sum), though you'll need to be careful with how you handle non-additive measures.

Upvotes: 2

Related Questions