Forum Discussion

Burningsuit's avatar
Burningsuit
Icon for Resident Rockstar rankResident Rockstar
9 years ago
Solved

DAX "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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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,

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

Hi Burningsuit,

 

How is your Month column calculated?

 

Regards,

 

MFelix

Burningsuit's avatar
Burningsuit
Icon for Resident Rockstar rankResident Rockstar
9 years ago

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's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      Burningsuit
      Icon for Resident Rockstar rankResident Rockstar
      9 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        9 years ago

        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