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,

Anonymous's avatar
Anonymous
Not applicable
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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.