topic Re: Help Creating Measure Count of top 5 appearance over 13 weeks in DAX Commands and Tips

Help Creating Measure Count of top 5 appearance over 13 weeks

AdamJo — Tue, 30 Dec 2025 23:11:44 GMT

Hi, I have a dataset with a duration of an event and a description. The description is one of a certain fixed set of text values and the duration is in hours. I also have for each event a weekand year number in the following format 202551, meaning week 51 2025. I am attempting to create a measure that can count how many times causes appear in the weekly top 5 (Ordered by duration), so that i can create a visual with the count on my y axis and the description on the x. I have a slicer that works to fix the current week I am looking at for other visuals, and want the measure to use this as the reference for counting back my 13 weeks.

To me this seems like a relatively simple problem from at least a logic or skeleton code perspective, but I cannot figure out how to make this work, I must caveat that I am new to Dax and come from a more coding specific experience such as Cpp and python.

The skeleton Logic I have is as follows:

Cause count
End week =Currentweek

Start week = Current week-13

13 week group = Filter(Duration Table, week>start week && week<End week

i=0

For week in 13 week group:

FilteredWeek= Filter(Duration Table, week= i)

FilteredRank=Rank(FilteredWeek, Order by Failure Duration)

For event in filtered week:

If rank <5, Cause count+=1

Return Cause Count

then using a filter on a visual to split it by cause. It should return the count of how many times causes appear in the top 5 over 13 weeks, so the total count without filters should be 65, and then when I split by cause it should be able to be filtered for example to show the top 5.

I hope this Skeleton code makes sense, I am just struggling with how to implement this in a dax query and making it work, I fear it is my own limitations.

Thank you for any assistance, or if this is something that dax just cannot do maybe?

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Ahmed-Elfeel — Wed, 31 Dec 2025 02:43:30 GMT

Hi AdamJo,

I hope you are doing well today ❤️☺️

Try this Dax Measure below which dynamically takes the selected week from the slicer and looks back 13 weeks and counts how many times each cause appears in the Top 5 (ordered by Duration):

Cause Top5 Count (13w) := VAR SelWeekIndex = MAX( Week[WeekIndex] ) // drives the window VAR StartWeekIndex = SelWeekIndex - 12 // 13-week filtered duration table (respects other slicers, removes Week filters) VAR FilteredDuration = CALCULATETABLE( 'Duration Table', ALL( Week ), Week[WeekIndex] >= StartWeekIndex, Week[WeekIndex] <= SelWeekIndex ) // Pre-aggregate once for performance VAR Summary13 = SUMMARIZE( FilteredDuration, 'Duration Table'[WeekCode], 'Duration Table'[Description], "TotalDuration", SUM( 'Duration Table'[DurationHours] ) ) VAR ThisCause = SELECTEDVALUE( 'Duration Table'[Description] ) // distinct week list from the pre-agg (only weeks that actually have data) VAR DistinctWeeks = DISTINCT( SELECTCOLUMNS( Summary13, "WeekCode", 'Duration Table'[WeekCode] ) ) RETURN IF( ISBLANK( ThisCause ), // dynamic total: sum actual top-N counts per week (<= 13*5) SUMX( DistinctWeeks, VAR CW = [WeekCode] VAR Top5 = TOPN( 5, FILTER( Summary13, [WeekCode] = CW ), [TotalDuration], DESC ) RETURN COUNTROWS( Top5 ) ), // per-cause: count weeks where the cause is in that week's top5 SUMX( DistinctWeeks, VAR CW = [WeekCode] VAR Top5 = TOPN( 5, FILTER( Summary13, [WeekCode] = CW ), [TotalDuration], DESC ) RETURN IF( COUNTROWS( FILTER( Top5, [Description] = ThisCause ) ) > 0, 1, 0 ) ) )

Replace table and column with your actua names if they different

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

cengizhanarslan — Wed, 31 Dec 2025 06:46:11 GMT

Please try the logic below:

Top5 Weeks Count (13w) = VAR EndDate = MAX ( 'DimDate'[Date] ) -- driven by your week slicer VAR Weeks = CALCULATETABLE ( VALUES ( 'DimDate'[YearWeek] ), DATESINPERIOD ( 'DimDate'[Date], EndDate, -13, WEEK ) ) RETURN SUMX ( Weeks, VAR ThisWeek = 'DimDate'[YearWeek] VAR Top5CausesThisWeek = TOPN ( 5, CALCULATETABLE ( SUMMARIZE ( Events, Events[Cause], "Dur", [Total Duration] ), KEEPFILTERS ( 'DimDate'[YearWeek] = ThisWeek ) ), [Dur], DESC ) RETURN IF ( CONTAINS ( Top5CausesThisWeek, Events[Cause], SELECTEDVALUE ( Events[Cause] ) ), 1, 0 ) )

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

v-sgandrathi — Wed, 31 Dec 2025 09:16:20 GMT

Hi AdamJo,

The solution provided by Ahmed-Elfeel above will meet the requirement.
Here are some key points to consider:

Ensure you use a valid Week dimension with a continuous numeric WeekIndex, as the 13-week lookback logic relies on a sequential index. If your data is missing weeks, it’s best to use a Date/Week table instead of calculating directly from the fact table.

The week slicer should be based on the Week table, not the duration/event table, to ensure the selected week correctly drives the 13-week window for all visuals.

The measure aggregates duration at the Week and Cause level before ranking, which ensures it identifies the weekly Top 5 causes by total duration as required.

Keep in mind, the overall total may be less than 65 if some weeks have fewer than five causes, which is normal and not a calculation error.

To check the logic, you can create a temporary table visual with Week, Cause, Total Duration, and the measure to see which causes are in the Top 5 each week.

Thank you.

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

sarahlns — Fri, 02 Jan 2026 16:54:22 GMT

Hi, this is a good use case for DAX, but the difficulty comes from trying to think in an imperative / loop-based way.
DAX is set-based, so instead of looping over weeks, you need to build virtual tables and aggregate over them.

The key idea is:

Identify the last 13 weeks based on the selected week
For each week, rank events by duration
Keep only the Top 5 per week
Count how many times each cause appears across those weeks
No explicit loops are needed.
Step 1 – Get the selected (current) week
Assuming you have a slicer on YearWeek (e.g. 202551):
Selected Week := MAX ( Events[YearWeek] )

Step 2 – Build the 13-week window

Weeks_13 := FILTER ( ALL ( Events[YearWeek] ), Events[YearWeek] <= [Selected Week] && Events[YearWeek] > [Selected Week] - 13 )

Step 3 – Rank events by duration within each week

Rank In Week := RANKX ( FILTER ( ALL ( Events ), Events[YearWeek] = MAX ( Events[YearWeek] ) ), Events[Duration], , DESC, DENSE )

Step 4 – Count appearances in weekly Top 5
This is the measure you put on the Y-axis, with Description (Cause) on X-axis:

Top 5 Appearances (13 Weeks) := VAR CurrentWeek = [Selected Week] VAR EventsInScope = FILTER ( ALL ( Events ), Events[YearWeek] <= CurrentWeek && Events[YearWeek] > CurrentWeek - 13 ) VAR Top5PerWeek = FILTER ( ADDCOLUMNS ( EventsInScope, "WeekRank", RANKX ( FILTER ( EventsInScope, Events[YearWeek] = EARLIER ( Events[YearWeek] ) ), Events[Duration], , DESC, DENSE ) ), [WeekRank] <= 5 ) RETURN COUNTROWS ( Top5PerWeek )

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Praful_Potphode — Sat, 03 Jan 2026 13:31:37 GMT

Hi AdamJo ,

Please try measure below:

Top 5 Cause Count v2 = VAR CurrentWeek = SELECTEDVALUE('Duration Table'[WeekYear]) VAR StartWeek = CurrentWeek - 13 // Count how many times this cause appears in top 5 across all weeks VAR Result = SUMX( VALUES('Duration Table'[WeekYear]), VAR WeekToCheck = 'Duration Table'[WeekYear] VAR IsInRange = WeekToCheck > StartWeek && WeekToCheck <= CurrentWeek VAR CurrentCause = SELECTEDVALUE('Duration Table'[Description]) VAR WeekTotal = CALCULATE( SUM('Duration Table'[Duration]), 'Duration Table'[WeekYear] = WeekToCheck, 'Duration Table'[Description] = CurrentCause ) VAR RankInWeek = RANKX( FILTER( ALL('Duration Table'[Description]), CALCULATE( SUM('Duration Table'[Duration]), 'Duration Table'[WeekYear] = WeekToCheck ) > 0 ), CALCULATE( SUM('Duration Table'[Duration]), 'Duration Table'[WeekYear] = WeekToCheck ), , DESC, DENSE ) RETURN IF(IsInRange && RankInWeek <= 5, 1, 0) ) RETURN Result

Sample PBIX

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

v-sgandrathi — Mon, 05 Jan 2026 04:35:54 GMT

Hi AdamJo,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Than you.

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

v-sgandrathi — Thu, 08 Jan 2026 04:30:33 GMT

Hi AdamJo,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

Thank you.

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Olufemi7 — Sat, 10 Jan 2026 17:00:09 GMT

Hi AdamJo,

You’re thinking about this exactly the right way the trick is that DAX doesn’t use loops like C++ or Python, but instead works through filter contexts and iterators.

You can absolutely build the measure you want. Here’s a step‑by‑step approach:

1. Create a proper week index

Your WeekYear values like 202551 are fine for display, but subtracting 13 won’t work correctly across year boundaries. The robust way is to build a date table and add a continuous week index:

WeekIndex = YEAR ( 'Date'[Date] ) * 100 + WEEKNUM ( 'Date'[Date], 2 )

This gives you a numeric key that increases continuously across years. Use this in your slicer instead of the concatenated text.

2. Rank events within each week

Add a calculated column to rank events by duration per week:

RankPerWeek = RANKX ( FILTER ( Duration, Duration[WeekIndex] = EARLIER ( Duration[WeekIndex] ) ), Duration[DurationHours], , DESC )

3. Flag the top 5

Create a flag column:

IsTop5 = IF ( [RankPerWeek] <= 5, 1, 0 )

4. Count appearances over the last 13 weeks

Now the measure that does the counting:

Top5Appearances = CALCULATE ( SUMX ( Duration, [IsTop5] ), FILTER ( Duration, Duration[WeekIndex] >= MAX ( Duration[WeekIndex] ) - 13 && Duration[WeekIndex] <= MAX ( Duration[WeekIndex] ) ) )

5. Use in your visual

Put Description on the X‑axis.
Put Top5Appearances on the Y‑axis.
The slicer for current week drives the MAX(Duration[WeekIndex]).
The measure looks back 13 weeks and counts how many times each cause was in the top 5.

The total count across all causes will be 65 (13 weeks × 5 causes), unless some weeks have fewer than 5 events. Splitting by description will show the breakdown per cause.

In summary: yes, DAX can do exactly what you want. The key is to use a proper week index, rank with RANKX, flag the top 5, and then sum over the last 13 weeks relative to your slicer. That will give you the measure you need for your visual.

topic Re: Help Creating Measure Count of top 5 appearance over 13 weeks in DAX Commands and Tips

Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Step 1 – Get the selected (current) week

Step 2 – Build the 13-week window

Step 3 – Rank events by duration within each week

Step 4 – Count appearances in weekly Top 5

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

Re: Help Creating Measure Count of top 5 appearance over 13 weeks

1. Create a proper week index

2. Rank events within each week

3. Flag the top 5

4. Count appearances over the last 13 weeks

5. Use in your visual