Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
drosencrans
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
Anonymous
Not applicable

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 😛

 

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

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

 

?

 

View solution in original post

7 REPLIES 7
v-yulgu-msft
Employee
Employee

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.

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

 

 

 

 

 

 

 

 

Anonymous
Not applicable

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

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

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
Not applicable

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 😛

 

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

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

 

?

 

Hey Scott-

 

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

 

Thanks for the direction!  Helps a lot.

 

Doug

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.