Sort by column affects filter context

[Anonymous]

Hi all,

We bumbed into a following issue few weeks back: when you use column that has mofied sort order both the original column and the sorting column affects the filter context.

Simple example to illustrate:

I'm calculating the number of rows (days) in Calendar-table by month. When 'Calendar'[Month] is not sorted, #All calendar rows -measure gives the expected result (all rows in a table). But when 'Calendar'[Month] is sorted by 'Calendar'[MonthNumber] both columns affect the filter context.

Therefore when [Month] -column is sorted I need to use #All calendar rows_monthnumber -measure to get all the rows in the table.

Measures:

#Calendar rows = COUNTROWS('Calendar')

#All calendar rows = CALCULATE(COUNTROWS('Calendar'), ALL('Calendar'[Month]))

#All calendar rows_monthnumber = CALCULATE(COUNTROWS('Calendar'), ALL('Calendar'[Month], 'Calendar'[Month Number]))

When [Month] is not sorted:

When [month] is sorted by [MonthNumber]:

I'm not sure to treat this as a bug or a feature. I haven't seen any documentation about this kind of behaviour and it came across as a surprise. Any thoughts on this?

[Rfranca]

hi, @Anonymous

look well
With the FEB / 2018 version some strange behavior appeared.
I believe Microsoft should send a fix for this the other items.

Best Regards,
Rfranca

[Anonymous]

Hi,

This issue was present already for earlier versions of PBI Desktop.

[JHob]

Hi BI for the people

Is this still happening for you? Have you found another way around it?

I think I am getting the same thing, when I add a sort column then my stacked area chart which calculates a % of sales for a particular category only uses the first item in the sort when I calculate sales/allselected(sales) which is resulting in stacked area chart with only the first selected item at 100%.

Any ideas would be great!

[JHob]

Found the solution

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/