How can we improve Power BI?

Better Handle Column Types on Load in Dimension Tables

When you load a dimension table (say a calendar table) in Power BI desktop, there are often lots of numeric columns that are not additive. Examples include Year, Month Number, Day of Week etc. By default (unless you set the data type to text) these numeric columns will have the default aggregation behaviour set to SUM. But they are not additive (of course). It would be much better to set these (dimension numeric columns) as "do not summarize". This could be managed in various ways that would be better than the current state. eg, you could simply mark all numeric columns in a calendar table as "do not summarize" by default and allow the user to change truly additive days (like isWorkingDay) to SUM when required. Even better, use the AI capabilites of Azure to work it out.

This idea was mentioned by Jeffery Wang in a meeting at the end of the MVP Summit 2019. No doubt Jeffery has his own ideas of how to execute this. It is a great idea and would add a lot of value to the majority of users.

160 votes
Sign in
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

Matt Allington shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: facebook google
Signed in as (Sign out)
  • Anonymous commented  ·   ·  Flag as inappropriate

    it's not just the calendar Table. Numeric Index columns also pose the same problem.

  • Nathan Peterson commented  ·   ·  Flag as inappropriate

    It's really not all that bad currently. On the Model view, you can select all the columns in a table and set "Summarize By" to "None". This accomplishes the desired behavior without too much pain.

    That said, this would be nice, so I voted. Perhaps there could be a way specify whether a table is a dimension table and act accordingly.

  • Daniel Bartley commented  ·   ·  Flag as inappropriate

    Since calendar are a author-once, ruse everywhere table, I’d like to see a scenario that makes it easier to reuse a calendar table where you manually set the default aggregation once and then reuse the calendar.

    Something like a library of frequently reused tables?

Feedback and Knowledge Base

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.