cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Determine Work Week of First Instance of Entry

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.

1 ACCEPTED SOLUTION
Super User

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

``````First Instance =
VAR SOW_FirstWorkWeek =
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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
2 REPLIES 2
Helper I

This worked beautifully.  Thank you.

Super User

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

``````First Instance =
VAR SOW_FirstWorkWeek =
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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog