Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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])))
?
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
Hello Yuliana Gu,
Thanks for your assistance!
I've uplaoded sample data here:
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
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,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |