cancel
Showing results for 
Search instead for 
Did you mean: 
0

Bug with Power BI Desktop ALL() function

I believe there is a bug in Power BI Desktop (tested in May 2017) with the ALL function.

 

Here is the scenario (using Adventure Works)

 

I place Calendar[Day Name] on rows on a visual.

I write a measure [Total Sales]

I write the following measure to return all the sales for all days.

Total All Day sales = CALCULATE([Total sales],all('Calendar'[DayName])) 

 

The above measure works as long as I have not used "sort by column" in the calendar table.  If the column Calendar[DayName] uses the native sort, then the above measure works.  If I change the default sort column to Calendar[DayNumberOfWeek]) then the above measure does not work.

 

If I write the following measure then it does work

Total All Day sales = CALCULATE([Total sales],all('Calendar'[DayName]),all('Calendar'[DayNumberOfWeek]))

 

But the addition of Calendar[DayNumberOfWeek] should not be needed inside the ALL function


Here is a sample workbook to demonstrate the problem.  https://www.dropbox.com/sh/ulfwt1eo6dfxf3b/AACGQF6zt7y2WvuZUbLkcVL8a?dl=1


Please confirm if you agree this is a bug.

 

Status: Delivered
Comments
Microsoft

@MattAllington

 

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 38248562
I’ll post here once I get any update about it.

 

Best Regards,
Herbert

Impactful Individual
Status changed to: Accepted
 
Microsoft

@MattAllington

 

I’ve got response from the Product Team.

 

This is by design. When a column [A] is sorted by another column [B], adding [A] to a report implicitly also add [B] to the report even though [B] is invisible. As a result, the measure must apply ALL to both [A] and [B].

 

Best Regards,
Herbert

Impactful Individual
Status changed to: Delivered
 
MVP

I understand the frustration, but it's complex to solve without changing the language.

DAX doesn't have a "sort by column" feature, it's up to the client to manage that.

When you include "Sort by column" in the model, the DAX client (Power BI) adds a ORDER BY column in the DAX query, and because the column sorted must be part of the DAX query, it is also part of the group by.

If your measure has an ALL statement on a column sorted, it removes the filter on such a column, but not on the underlying one.

The behavior is different in Excel, because it generates MDX queries, and in that case the sort order is implicit and a different behavior between Excel 2013 and Excel 2016 could have been done to align the behavior of Excel to the one of Power BI.

The solution is not easy, because if the client (Power BI) changes the query, it's hard to guarantee that it will generate the expected behavior. If DAX modifies its behavior (for example when you apply ALL over a column sorted) this could break existing reports/queries, and we should discuss about the behavior of ALLEXCEPT, too.

Probably, the behavior should change in DAX to make it more intuitive, but it could have undesired side effects. I can imagine many existing custom time intelligence formulas that could be broken by that (not sure, but evaluating the possible impacts is not easy).

 

Resident Rockstar

Hi, 
Still this bus is there. let me confirm it is solved.

Resident Rockstar

All in _rdo.PNG

 

 

selected _rdo.PNG

 

 

 

MVP

The Dev team (and Marco Russo) confirmed this is not a bug. It is working as designed and it is complex to change the behaviour.