Forum Discussion

drosencrans's avatar
drosencrans
Frequent Visitor
9 years ago
Solved

Help with KEEPFILTERS within Calculation(Average)

My current problem: I have a measure from a summarized table for Location, Date, WeekNumber, Month, and Day based on MaxTransactionsDay.

 

I then need to create a new measure for AvgWeek:

AvgTWeek =CALCULATE(AVERAGE('AvgTTable'[MaxTDay]),FILTER(ALL('AvgTTable'),'AvgTTable'[WeekNumber]=MAX('DimDate'[WeekNumber])))

The problem I am facing is the removal of the report filters:  Location, Day (I exclude Sat/Sun).  WeekNumber is filtered.

I've tried to use KEEPFILTERS, but can't seem to get the function to work.

 

Any help as that seems to be the correct route to take as I don't won't the filters removed that is caused by calculate.

 

Thanks,

Doug

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    I'm 97% sure you are making this more complicated than necessary.  In as much as that I have been writing DAX for closer to 4 years and have no idea what KEEPFILTERS() even does :-P

     

    Not to ask a crazy question, but what is wrong w/ :

    AvgMeasureWeek = CALCULATE(AVERAGE('Table'[MaxPerDay]),FILTER(ALL('Date'),'Date'[WeekNum]=MAX('Date'[WeekNum])))

     

    ?

     

7 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi drosencrans,

     

    From current description, I was confused about your requirement. Would you please share some sample data in the summarized table and show us the result with the measure for AvgWeek? Besides, please elaborate what does "the removal of the report filters" mean.

     

    If possible, you can post an image to show your desired output.

     

    Best regards,
    Yuliana Gu

    • drosencrans's avatar
      drosencrans
      Frequent Visitor
      9 years ago

      Hello Yuliana Gu,

       

      Thanks for your assistance!

       

      I've uplaoded sample data here:

      Sample Data

       

      If you can't connect the data looks like:

       

       

       

      The PBI Data and formula:

      Table = SUMMARIZE('Transaction','Transaction'[Location],'Transaction'[Date],'Transaction'[WeekNum],"MaxPerDay",MAX('Transaction'[Transactions]))

       

      AvgMeasureWeek = CALCULATE(AVERAGE('Table'[MaxPerDay]),FILTER(ALL('Table'),'Table'[WeekNum]=MAX('Date'[WeekNum])))

       

      I need the AvgMeasureWeek to include all reporting filters, i.e. Location, Year, Weekday as I exclude data like Sat/Sun from the result.

       

      Thanks,

      Doug

       

       

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        ALL('Table') is going to remove... well, all the filters from the table.  If you want to keep some... use ALLEXCEPT, and specify which columns you want to keep the filters on...