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...
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])))

     

    ?

     

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...

  • drosencrans's avatar
    drosencrans
    Frequent Visitor
    9 years ago

    Thanks!

     

    How would that be represented, so that, I don't remove the currently selected report filters, but ensure that WeekNum is an additivie filter in the formula only?  I must be missing something simple?

     

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

    • drosencrans's avatar
      drosencrans
      Frequent Visitor
      9 years ago

      With the suggestions and additonal review of documentation - wow....

       

      Looks like this is an appropriate solution:

      AvgMeasureWeek3 = CALCULATE(AVERAGE('Table'[MaxPerDay]),KEEPFILTERS(VALUES('Table'[MaxPerDay])),FILTER('Table','Table'[WeekNum]=MAX('Date'[WeekNum])))

       

      As I am gettinig correct results.

       

      Can anyone comfirm?

       

      Thanks,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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])))

         

        ?