Matt Allington on 22 Mar 2019 21:05:17
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.
- Comments (3)
RE: Better Handle Column Types on Load in Dimension Tables
it's not just the calendar Table. Numeric Index columns also pose the same problem.
RE: Better Handle Column Types on Load in Dimension Tables
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.
RE: Better Handle Column Types on Load in Dimension Tables
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?