ALL/ALLEXCEPT problem

[DLU]

Dear community, 

I'm working through the Supercharge PowerBI book and I'm running into something that I just don't understand. 

I have a matrix with Country as a filter and I'm comparing the difference between using the ALL function to calculate the total sales amount with the entire table versus just one column with the following measures: 

Total Global Sales = CALCULATE([Total Sales];ALL(Territories))

Total All Country Sales = CALCULATE([Total Sales];ALL(Territories[Country]))

The outcome is in fact the same. 

I'm now trying this on a different matrix with Daynames as a filter with the following measures: 

Total Calendar Sales = CALCULATE([Total Sales];ALL('Calendar'))

Total Days Names Sales = CALCULATE([Total Sales];ALL('Calendar'[DayName]))

The outcome isn't the same and I can't figure out why. The only difference I'm seeing is that the tablename Calendar is surrounded by ', and the Territories table isn't, I don't know why. And I just don't understand why the outcome isn't the same. I'm referring to the same column name as the filter in the matrix is, so why am I not getting the same results? The function should have removed the filter from the matrix (daynames), but it just doesn't. Can anyone explain why this is happening? I really appreciate your help!

With kind regards, 

DLU

[johnt75 · Accepted solution]

You are indeed correct, its using the 'Calendar'[DayNumberOfWeek] column. The reason is that the 'Calendar'[DayName] column is sorted by 'Calendar'[DayNumberOfWeek], so Power BI has to include that in the underlying summary table so that it can display it correctly in the visual.

If you add a REMOVEFILTERS or ALL on 'Calendar'[DayNumberOfWeek] to your measure as well as the existing one on 'Calendar'[DayName] then that should give you the results you are expecting.

[bcdobbs]

When used like that in CALCULATE, ALL should be thought of as "Remove Filters From". In fact there is a function called REMOVEFILTERS which is just an alias of all that can only be used inside calculate. Inside calculate it doesn't return all values it removes all directly applied filters.

ALL ( TableName ) removes all filters from the table. ALL ( TableName[ColumnName] ) removes filters that are applied just to that column. It doesn't remove filters applied

to other columns in the same table.

So as @johnt75 suggests there are other filters present on the date table but not on your territory table. Eg you're removing any filters directly applied to dayname but if there is a filter on year that would still be present.

[johnt75]

The ' surrounding Calendar is because CALENDAR is a DAX function, so the ' is necessary to distinguish your Calendar table from the function. Don't worry about, it isn't causing the problem.

It sounds like more filters are being applied than you are aware of, Power BI sometimes creates local date tables, which you can't see in Power BI Desktop, and it could be applying a filter to that.

To see exactly what filters are being applied, open Performance Analyzer from the View ribbon, click Start Recording and then Refresh Visuals. Expand the entry for your matrix visual and copy the code. You can then paste it into DAX Studio, or any text editor, and examine exactly which filters are being applied.

[DLU]

Dear johnt75, 

Thank you for your quick response. I've followed the steps like you described and this was the outcome within DAX Studio: 

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('Calendar'[DayName], 'Calendar'[DayNumberOfWeek]), "IsGrandTotalRowTotal"
      ),
      "Total_Sales", 'Sales'[Total Sales],
      "Total_Sales_for_All_Days_Selected_Dates", 'Sales'[Total Sales for All Days Selected Dates],
      "v__Sales_for_All_Days_Selected_Dates", 'Sales'[% Sales for All Days Selected Dates],
      "Total_Calendar_Sales", 'Sales'[Total Calendar Sales],
      "Total_Days_Names_Sales", 'Sales'[Total Days Names Sales]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'Calendar'[DayNumberOfWeek],
      1,
      'Calendar'[DayName],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Calendar'[DayNumberOfWeek], 'Calendar'[DayName]

Am I interpreting this correctly that it also does something with Calendar DayNumberOfWeek? If so, could you explain to me why this is happening and what it's doing? And just for your information. The calendar table isn't one created with DAX, it was imported from an excelfile. And I've also disabled the ability for PowerBI to create it's own datetables. Many thanks again!

Gr DLU

[johnt75 · Accepted solution]

You are indeed correct, its using the 'Calendar'[DayNumberOfWeek] column. The reason is that the 'Calendar'[DayName] column is sorted by 'Calendar'[DayNumberOfWeek], so Power BI has to include that in the underlying summary table so that it can display it correctly in the visual.

If you add a REMOVEFILTERS or ALL on 'Calendar'[DayNumberOfWeek] to your measure as well as the existing one on 'Calendar'[DayName] then that should give you the results you are expecting.

[DLU]

That worked indeed johnt75, thank you! What I'm just not following is why PowerBI is using DayNumberOfWeek as a filter, since it's only used for sorting. So I would rather expect that the outcome would be the same, but the order might change or something. Is sorting considered also as some kind of sorting to then? I always like to understand why something is happening, so if you wouldn't mind giving me a little bit more insight? Many thanks again.

Gr. DLU

[johnt75 · Accepted solution]

In a table or matrix visual, the column and row headings are applied to each individual cell to calculate the value for that combination of filters, so if you had months in the row, and day of week in the columns, then for each cell it would use ( month, day of week ) as a combined filter.

Possibly the best way to think of sort columns is as if they were hidden headers, so they too get included in the filter, so in my previous example the filters applied would actually be ( month, month number, day of week, day of week number ) as each of the base columns requires a sort column to be included.

[DLU]

Thank you johnt75 for your clear explanation!