cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Sort by column affects filter context

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:

Sorted by month number.PNG

 

When [month] is sorted by [MonthNumber]:

Sorted by month number2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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?

4 REPLIES 4
Rfranca
Resolver IV
Resolver IV

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
Not applicable

Hi,

 

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

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!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!