Forum Discussion
Resident RockstarDAX "ALL" function not working on sorted columns
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?
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,
8 Replies
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,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
Super User
- 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
