cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drosencrans Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
scottsen Senior Member
Senior Member

Re: Help with KEEPFILTERS within Calculation(Average)

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 7
Community Support Team
Community Support Team

Re: Help with KEEPFILTERS within Calculation(Average)

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
drosencrans Frequent Visitor
Frequent Visitor

Re: Help with KEEPFILTERS within Calculation(Average)

Hello Yuliana Gu,

 

Thanks for your assistance!

 

I've uplaoded sample data here:

Sample Data

 

If you can't connect the data looks like:

Sample Data.png

 

 

 

The PBI Data and formula:

PBI2.png

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

 

 

 

 

 

 

 

 

scottsen Senior Member
Senior Member

Re: Help with KEEPFILTERS within Calculation(Average)

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 Frequent Visitor
Frequent Visitor

Re: Help with KEEPFILTERS within Calculation(Average)

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 Frequent Visitor
Frequent Visitor

Re: Help with KEEPFILTERS within Calculation(Average)

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,

 

scottsen Senior Member
Senior Member

Re: Help with KEEPFILTERS within Calculation(Average)

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

 

?

 

Highlighted
drosencrans Frequent Visitor
Frequent Visitor

Re: Help with KEEPFILTERS within Calculation(Average)

Hey Scott-

 

You are right!  Replacing Table with Date fixed the complexity I was working around.

 

Thanks for the direction!  Helps a lot.

 

Doug