Determine Work Week of First Instance of Entry

[emilymgoitia]

Hello.

I have some data that looks like this:

SOW	Work Week Number
2003	9
2003	9
2003	9
2007	9
2007	9
7921	9
7913	9
2003	10
2003	10
2003	10
2007	10
2007	10
7921	10
2003	11
2003	11
2003	11
2003	11
2003	11
2007	11
2007	11
7921	11
2003	12
2003	12
2003	12
2003	12
2003	12
11661	12
11661	12
2003	13
2003	13
2003	13
2007	13
2007	13
7921	13
11661	13
11661	13
11661	13
11661	13
11661	13
11661	13
11661	13
2003	14
2003	14
2003	14
2003	14
2007	14
2007	14
7921	14
11661	14
11661	14
11661	14
11661	14
2003	15
2003	15
2003	15
2007	15
2007	15
7921	15
11371	15
11371	15
11371	15
11661	15
11661	15
11661	15
11661	15
2003	16
2003	16
2007	16
2007	16
7921	16
11371	16
11371	16
11371	16
11371	16
11661	16
11661	16
11661	16

There are multiple instances of a scope number, along with the work week that the data was pulled.  
I want to a count of how many scopes are added each week, which would be a unique count of the first instance of the SOW.
I'm hoping for results that look like this:

SOW	Work Week Number	First Instance
2003	9	1
2007	9	1
7913	9	1
7921	9	1
11661	12	1
11371	15	1

In this example, I would have 4 new SOW in work week 9, 1 in work week 12 and 1 in work week 15.  
What is the best way to accomplish this?

Thanks.

[emilymgoitia]

This worked beautifully.  Thank you.

[OwenAuger · Accepted solution]

Hi @emilymgoitia 

Here is a measure that should do what you are looking for (replace Data with your actual table name):

First Instance = 
VAR SOW_FirstWorkWeek = 
    ADDCOLUMNS ( 
        VALUES ( Data[SOW] ),
        "@FirstInstance",
        CALCULATE ( MIN ( Data[Work Week Number] ), ALLEXCEPT ( Data, Data[SOW] ) )
    )
VAR SOW_FirstWorkWeekWithLineage = 
    TREATAS ( 
        SOW_FirstWorkWeek,
        Data[SOW],
        Data[Work Week Number]
    )
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( Data[SOW] ),
        KEEPFILTERS ( SOW_FirstWorkWeekWithLineage )
    )

This is a similar pattern to some of the measures on Semi-additive calculations - DAX Patterns 

Regards,

Owen