Bug in the top N function?

[cyber_sinh]

Hi,

I'm trying to show only the last 5 last years of data (2013-2017). So, I used the TOP N feature to filter unwanted years but if I set 5 in top N feature, the year 2012 is not filtered and the year 2013 appears empty...

You will find below a screencast showing the issue:

https://www.dropbox.com/s/bjeu0s49158mzp1/topn_powerbi_issue.gif?dl=0

Is it a bug or am I missing something?

Thanks.

[v-haibl-msft]

@cyber_sinh

Does above solution help? Please let me know if you still have question about it.

Best Regards,
Herbert

[cyber_sinh · Accepted solution]

Hi,

To solve the issue, I used the date column (and not the year) of the same ref_dates table (using the date column of the 'fact' table didn't work), filtered with the new relative date (last 5 years) and the missing data were appeared as expected.

I still don't understand why filtering directly with the year colum has a such unpredictable behavior.

Thanks for your help.

Regards,

Cyber Sinh

[v-haibl-msft]

@cyber_sinh

I've got the technical explanation from experts.

This isn't an issue in PowerBI Desktop, it is an artifact of the way the model is setup. At this point this is by design and I will provide the technical explanation. Please see the attached _Fixed version of the report showing the visuals that I will detail about.

The TOPN filter is structured as follows: Top 5 Calendar[Year] by Calendar[Year]. What happens under the hood is we will group Calendar[Year] and compute SUM(Calendar[Year]) to order the group instance values by. This is the design on TopN, group and compute a measure to order by it. When we compute SUM(Calendar[Year]) the values returned per year are not the years themselves. Please see in the _Fixed version of the PBIX on the left side I built a table that contains the same fields as the TopN filter would. Observe how the right side column besides the year values have very large values. That is because the SUM is adding up all the instances of each year in the Calendar table. This happens because Year is not a unique column and it is a computed column. Basically the way that works is we take all instances of 2008 for example and when we compute the SUM we add all of the 2008 instances which results in a very high value. When you look at the values that result from that SUM, the top 5 are the years that are shown when applying the TopN filter.

The difference between Calendar[Year] and Table1[Year] is that Table1[Year] has unique values of Year so the SUM results in the same value as the group. See the lower left side table that reflects that.

Best Regards,
Herbert

[v-haibl-msft]

@cyber_sinh

Please use the Year column of Fact table instead of the Year column in Calendar (ref_dates) table. Please refer to following screeenshot.

Best Regards,
Herbert