topic Re: Recalculate max based on filters in DAX Commands and Tips

Recalculate max based on filters

mateoc15 — Mon, 21 Sep 2020 13:53:11 GMT

I am trying to create a gauge that shows the current week vs. the 13 week maximum. If in the last 13 weeks the maximum weekly value is 20 and the current week is 15 then the gauge would be 75% "full". It's working as expected until I apply filters. The value is filtered correctly, but the 13 week maximum is not recalculating. Here's what I'm currently using that is problematic:

Visitors - Weekly Max All Time = MAXX( SUMMARIZE(ALL('Traffic'), 'Date'[Week Of], "DistinctCookie", DISTINCTCOUNT(Traffic[cookie_id])), [DistinctCookie])

I tried this ALLEXCEPT filter but it behaves the same:

Visitors - Weekly Max All Time = MAXX( SUMMARIZE(ALL('Traffic'), 'Date'[Week Of], "DistinctCookie", CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]), ALLEXCEPT('Traffic', 'Date'[Week Of]))), [DistinctCookie])

Placing the ALLEXCEPT as the first parameter of the SUMMARIZE doesn't seem to work either.

Any help appreciated. Thank you!

Re: Recalculate max based on filters

mateoc15 — Mon, 21 Sep 2020 13:50:12 GMT

Re: Recalculate max based on filters

amitchandak — Mon, 21 Sep 2020 14:28:56 GMT

mateoc15 , You need rolling 91 Days or 13 weeks for week you can week rank in table have measure like given below

column in date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 13 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

refer my blogs if they can help

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Re: Recalculate max based on filters

mateoc15 — Mon, 21 Sep 2020 14:40:22 GMT

amitchandak Maybe I wasn't clear, I'm sorry. The date is not the problem. I have that figured out via a "Week Of in the last 13 calendar weeks" filter (which the user can't change). The problem is the measure for the max value. The 1.09M is total for all records, but if I filter on any dimension in the Traffic table I want it to recalculate the max value, applying that filter across the board. Suppose the filter is favorite color. If I select blue, and if the maximum weekly value is only 300k people with a favorite color of blue, then the high end of the gauge should show 300k and the value should show however many people have a favorite color of blue in the current week (maybe 250k or something as an example). These filters are user-selected, not via DAX.

Re: Recalculate max based on filters

amitchandak — Mon, 21 Sep 2020 16:53:30 GMT

mateoc15 , Allexpect will not work with summarize. You can use addcolumn on top summarize to make it work

or try like

Visitors - Weekly Max All Time =
MAXX(values('Date'[Week Of]) ,
CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]), ALLEXCEPT('Traffic', 'Date'[Week Of])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Recalculate max based on filters

mateoc15 — Mon, 21 Sep 2020 18:24:25 GMT

I finally got it. Although I'm still not 100% sure why, this works, based on some unrelated tinkering with this article about ADDCOLUMNS.

The old max value calculation:

Visitors - Weekly Max All Time = MAXX( SUMMARIZE( ALL('Traffic'), 'Date'[Week Of], "DistinctCookie", DISTINCTCOUNT(Traffic[cookie_id])), [DistinctCookie])

The new max value calculation:

Visitors - Weekly Max All Time = MAXX( ADDCOLUMNS( SUMMARIZE( ALL('Traffic'), 'Date'[Week Of]), "DistinctCookie", CALCULATE(DISTINCTCOUNT(Traffic[cookie_id]))), [DistinctCookie])

The scope/context of the SUMMARIZE is just different, and a CALCULATE without a second parameter.

amitchandak Maybe you can help explain why it makes a difference?

It's also important to note that any date filters must be on the "Week Of" value from the Date table, not any other field such as the exact date or month, etc.