DAX "ALL" function not working on sorted columns

[Burningsuit]

I have a problem with a DAX expression.  It’s when I want to use the ALL function on the Month column of my Date Table to show Total Sales across all months in each year.  If you look at the table on the right, you can see the expression for the “AllMonths2” measure which is calculating correctly.  You can also see that the expression also works if I use the MonthNo column instead (in the “AllMonths” measure in the table on the left)

 However, if I sort the Month column by the MonthNo column, I now get an incorrect result from the expression: -
Am I doing something wrong?

[v-yuezhe-msft · Accepted solution]

@Burningsuit,

What you describe is by design. When Month column is sorted by MonthNo column, adding Month column to a report also add MonthNo column to the report. Thus, the measure must apply ALL to both Month column and MonthNo column, in other words, you should create the measure using the DAX as below.

AllMonths2= CALCULATE([TotalSales],ALL(DateTable[Month]), ALL(DateTable[MonthNo]))

There is a issue for your reference:
https://community.powerbi.com/t5/Issues/Bug-with-Power-BI-Desktop-ALL-function/idi-p/181454

Regards,

[srikanthgunnam]

Hello,

I have a  matrix visual with 8 to 9 columns and have one page level filter(Values:1,2,4--Applied filter not in 4) and one report level filter(Applied filter sales not in US) applied to the report. Now, when ther user filter(Yes/No filter name) out of 8 to 9 columns, 3 columns values should not be effected in the matrix but when the user selects other filters, values should change. I have all the columns and filters coming from the same table.

Can anyone help me out in getting this?

I have tried using this

Calculate(DISTINCTCOUNT(column),All(Yes/No)) is not giving me the correct result.

[MFelix]

Hi @Burningsuit,

How is your Month column calculated?

Regards,

MFelix

[Burningsuit]

The Month column comes from a DateTable, no calculations are involved.

The date table looks (something) like this.

Date              Month    Monthno

30/06/2017   June        6

01/07/2017   July         7

02/07/2017   July         7

...........

and so on. 

The Transactions table looks (something) like this

Date              Sales

01/07/2017   123.12

01/07/2017   234.34

............

and so on.

Obviously the two are related via Date in the DataModel

TotalSales is a SUMX of Sales. However I've tried this with different DAX aggregations and the same problem exists.

[MFelix]

If the month colum comes from a  date table you should be able to add the Month and keep your calculation on month number and everything should work the same, since month and month name come from the same table.

Regards,

MFelix

[Burningsuit]

No, The calculation WORKS FINE, when the DateTable column "Month" is not sorted by "MonthNo", but of course the months are listed in alphabetical order. However when the "Month" column in the DateTable is sorted by "MonthNo" - in order to list the Months is the correct order, the calculation does NOT work. Why should this be ?

[MFelix]

Chenge your formula to Date instead of the month name/month number, your calculations will continue to work in the same way becasue the ALL formula in your calculation is a filter parameter that includes all the records in the date table.

See if making this change works no matter the sort order of the columns.

Regards,

MFelix

[Burningsuit]

No, @MFelix you misunderstand. The expression is ALL(DateTable[Month]) which simply removes the filter from the month column  to show the values for the entire year of the current filter context (e.g 2017). The data type does not affect the expression. I think we have a bug in DAX here. Look at the examples in my original post, it works when the column is not sorted by monthno. Using the sort by column option on the modeling tab produces the problem.