Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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
This worked beautifully. Thank you.
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
User | Count |
---|---|
122 | |
97 | |
89 | |
77 | |
68 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |