Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
emilymgoitia
Helper I
Helper I

Determine Work Week of First Instance of Entry

Hello.

I have some data that looks like this:

SOWWork Week Number
20039
20039
20039
20079
20079
79219
79139
200310
200310
200310
200710
200710
792110
200311
200311
200311
200311
200311
200711
200711
792111
200312
200312
200312
200312
200312
1166112
1166112
200313
200313
200313
200713
200713
792113
1166113
1166113
1166113
1166113
1166113
1166113
1166113
200314
200314
200314
200314
200714
200714
792114
1166114
1166114
1166114
1166114
200315
200315
200315
200715
200715
792115
1137115
1137115
1137115
1166115
1166115
1166115
1166115
200316
200316
200716
200716
792116
1137116
1137116
1137116
1137116
1166116
1166116
1166116


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:

SOWWork Week NumberFirst Instance
200391
200791
791391
792191
11661121
11371151

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
OwenAuger
Super User
Super User

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


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

View solution in original post

2 REPLIES 2
emilymgoitia
Helper I
Helper I

This worked beautifully.  Thank you.

 

OwenAuger
Super User
Super User

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


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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.