Help Creating Measure Count of top 5 appearance over 13 weeks

[AdamJo]

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?

[Ahmed-Elfeel · Accepted solution]

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.

[Olufemi7]

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.

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[Praful_Potphode]

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

[sarahlns]

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 )

[v-sgandrathi]

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.

[cengizhanarslan · Accepted solution]

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

[Ahmed-Elfeel · Accepted solution]

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.