Counting How Many Weeks A Duplicate Values Occurs Using Dax

A

adamzee

New Member
Joined
Apr 12, 2015
Messages
9
Hello I have 2 calculated columns in a power pivot model (key and week), Key consists of a store number and a demonstration product that is not operational in store. Week is when a store was visited by a sales rep.
I am looking to create another calculated column- Number of weeks not working, which counts how many weeks the key has appeared in the data. An example is below. The only catch is I am trying to reset the counter if the key does not appear in the previous week, an example of this is Key 29-product 1 in week 5 (highlighted red). This reverts back to a zero count for weeks not operational as Key 29-product 1 did not appear in week 4.

Any help would be appreciated, thanks.


[TABLE="width: 344"]
<tbody>[TR]
[TD]key
[/TD]
[TD]Week
[/TD]
[TD]*Number of weeks not working[/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]29-product1
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Apologies, 29-product 1 has a value in week 4 but that is a mistake. I cannot edit the original post. The corrected example is below.

[TABLE="class: cms_table, width: 344"]
<tbody>[TR]
[TD]key
[/TD]
[TD]Week
[/TD]
[TD]*Number of weeks not working[/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]29-product1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]29-product1
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29-product2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]41-product1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]55-product1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
So I think what you're looking for is a column calculating "consecutive weeks run length" for the current row's key, starting with BLANK<blank> for the first week of a run, and incrementing for subsequent weeks.

First off, this ain't pretty in DAX, and I think it would be easier to pre-compute in Power Query for example.

But...to do it in a calculated column with DAX, here is one suggestion (replace Table1 with your table name):

Code: 
=
MAXX (
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[key] = EARLIER ( Table1[key] )
                && Table1[Week] < EARLIER ( Table1[Week] )
        ),
        Table1[Week]
    ),
    IF (
        EARLIER ( Table1[Week] ) - Table1[Week]
            = COUNTROWS (
                SUMMARIZE (
                    FILTER (
                        Table1,
                        Table1[key] = EARLIER ( Table1[key], 2 )
                            && Table1[Week] < EARLIER ( Table1[Week], 2 )
                            && Table1[Week] >= EARLIER ( Table1[Week] )
                    ),
                    Table1[Week]
                )
            ),
        EARLIER ( Table1[Week] ) - Table1[Week]
    )
)
</blank>
  1. <blank>The first SUMMARIZE creates a table of weeks earlier than the current week for the current row's key.</blank>
  2. Then the code in the IF(...) works out (for each of the weeks from step 1) if there is a run of consecutive weeks from that week to the current row's week, and if so return that number as a potential run length.
  3. MAXX returns the largest of the potential run lengths.
 
Upvote 0
Ozeroth said:
So I think what you're looking for is a column calculating "consecutive weeks run length" for the current row's key, starting with BLANK<blank> for the first week of a run, and incrementing for subsequent weeks.

First off, this ain't pretty in DAX, and I think it would be easier to pre-compute in Power Query for example.

But...to do it in a calculated column with DAX, here is one suggestion (replace Table1 with your table name):

Code: 
=
MAXX (
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[key] = EARLIER ( Table1[key] )
                && Table1[Week] < EARLIER ( Table1[Week] )
        ),
        Table1[Week]
    ),
    IF (
        EARLIER ( Table1[Week] ) - Table1[Week]
            = COUNTROWS (
                SUMMARIZE (
                    FILTER (
                        Table1,
                        Table1[key] = EARLIER ( Table1[key], 2 )
                            && Table1[Week] < EARLIER ( Table1[Week], 2 )
                            && Table1[Week] >= EARLIER ( Table1[Week] )
                    ),
                    Table1[Week]
                )
            ),
        EARLIER ( Table1[Week] ) - Table1[Week]
    )
)
</blank>
  1. <blank>The first SUMMARIZE creates a table of weeks earlier than the current week for the current row's key.</blank>
  2. Then the code in the IF(...) works out (for each of the weeks from step 1) if there is a run of consecutive weeks from that week to the current row's week, and if so return that number as a potential run length.
  3. MAXX returns the largest of the potential run lengths.
Click to expand...

Ozeroth this worked perfectly. Thank you!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
DAX: Double or Nested COUNTX() & SUMX()
Replies
6
Views
532
luuk
L
Guinaba
  • Solved
DAX in Power Pivot - Replacing -> SELECTEDVALUE
Replies
1
Views
466
Matt Allington
M
E
  • Question Question
Convert Excel Formula to DAX Which Averages Last 2 Values Until Certain Period of Time
Replies
5
Views
488
ExcelToDAX
E
E
  • Solved
Is there a resolution or alternative when experiencing Power Pivot memory errors?
2
Replies
12
Views
722
ez08mba
E
RICH937
  • Question Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
445
RICH937
RICH937

Forum statistics

Threads
1,224,138
Messages
6,176,583
Members
452,738
Latest member
kylua

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top