Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
This is the data I have: (Grouped by Lot and Site)
Table1:
Lot_PK | Device | Lot | Site | Total Parts-Phase1 | Passed Parts-Phase1 | Yield-Phase1 | Total Parts-Phase2 | Passed Parts-Phase2 | Yield-Phase2 |
1 | D1 | L1 | 1 | 100 | 80 | 80 | 20 | 15 | 75 |
1 | D1 | L1 | 2 | 100 | 70 | 70 | 30 | 20 | 66.67 |
2 | D1 | L2 | 1 | 100 | 70 | 70 | 0 | 0 | 0 |
2 | D1 | L2 | 2 | 100 | 85 | 85 | 45 | 35 | 77.78 |
3 | D1 | L3 | 1 | 0 | 0 | 0 | 30 | 20 | 66.67 |
4 | D1 | L4 | 2 | 200 | 175 | 87.5 | 0 | 0 | 0 |
5 | D1 | L5 | 1 | 120 | 100 | 83.33 | 20 | 5 | 25 |
5 | D1 | L5 | 2 | 120 | 110 | 91.67 | 10 | 5 | 50 |
6 | D2 | L6 | 1 | 200 | 170 | 85 | 0 | 0 | 0 |
7 | D2 | L7 | 1 | 50 | 35 | 70 | 15 | 10 | 66.67 |
7 | D2 | L7 | 2 | 50 | 40 | 80 | 10 | 7 | 70 |
8 | D2 | L8 | 1 | 0 | 0 | 0 | 10 | 3 | 30 |
8 | D2 | L8 | 1 | 75 | 65 | 86.67 | 10 | 8 | 80 |
9 | D2 | L9 | 2 | 75 | 70 | 93.33 | 5 | 1 | 20 |
Same data is grouped by Lot (for understanding purpose only)
Table2:
Lot_PK | Device | Lot | Total Parts-Phase1 | Passed Parts-Phase1 | Yield-Phase1 (%) | Total Parts-Phase2 | Passed Parts-Phase2 | Yield-Phase2 (%) |
1 | D1 | L1 | 200 | 150 | 75 | 50 | 35 | 70 |
2 | D1 | L2 | 200 | 155 | 77.5 | 45 | 35 | 77.78 |
3 | D1 | L3 | 0 | 0 | 0 | 30 | 20 | 66.67 |
4 | D1 | L4 | 200 | 175 | 87.5 | 0 | 0 | 0 |
5 | D1 | L5 | 240 | 210 | 87.5 | 30 | 10 | 33.33 |
6 | D2 | L6 | 200 | 170 | 85 | 0 | 0 | 0 |
7 | D2 | L7 | 100 | 75 | 75 | 25 | 17 | 68 |
8 | D2 | L8 | 75 | 65 | 86.67 | 20 | 11 | 55 |
9 | D2 | L9 | 75 | 70 | 93.33 | 5 | 1 | 20 |
e.g. Here Total Parts-Phase1 = sum of column "Total Parts-Phase1" for Lot "L1". Same for other lots. Same principle works for other Total columns.
This is what I want:
Min Yield (By Lot)?
Max Yield (By Lot)?
These Min/Max yields are created at Lot level for each device as given below:
Table3:
Device | Total Parts-Phase1 | Passed Parts-Phase1 | Yield-Phase1 | Min Yield (By Lot) | Max Yield (By Lot) |
D1 | 840 | 690 | 82.14 | 0 | 87.5 |
D2 | 450 | 380 | 84.44 | 75 | 93.33 |
I also want these numbers:
Table4 (Calculated at Lot level):
Total # of Lots | 9 |
Total # of Lots with Both Phase1 & Phase2 | 6 |
Total # of Lots with Phase1 only | 2 |
Total # of Lots with Phase2 only | 1 |
How can I achieve data presented in Table3 and Table4 without creating Table2??
Solved! Go to Solution.
The EARLIER() function is here to the rescue. Basically, the syntax would look like this:
To get the total parts for L1, you can create a calculated column with this syntax:
=Calculate(Sum([Total Parts-Phase1]),FILTER('TABLE1','TABLE1'[LOT]=EARLIER('TABLE1'[LOT)))
This will create a calculated column that will give you the total parts for each lot essentially ignoring all other Columns. YOu can repeat this basic syntax if you wanted to do it at the device level. You can also combine filters if, say, you wanted to see the total by Device & lot. That syntax would look like this:
=Calculate(Sum([Total Parts-Phase1]),FILTER('TABLE1','TABLE1'[LOT]=EARLIER('TABLE1'[LOT)&&'TABLE1'[DEVICE]=EARLIER('TABLE1'[DEVICE])))
Now don't ask me how EARLIER() works. Just learn how to use it and enjoy the awesomeness. Just assume the mothership in Redmond got it right.
The EARLIER() function is here to the rescue. Basically, the syntax would look like this:
To get the total parts for L1, you can create a calculated column with this syntax:
=Calculate(Sum([Total Parts-Phase1]),FILTER('TABLE1','TABLE1'[LOT]=EARLIER('TABLE1'[LOT)))
This will create a calculated column that will give you the total parts for each lot essentially ignoring all other Columns. YOu can repeat this basic syntax if you wanted to do it at the device level. You can also combine filters if, say, you wanted to see the total by Device & lot. That syntax would look like this:
=Calculate(Sum([Total Parts-Phase1]),FILTER('TABLE1','TABLE1'[LOT]=EARLIER('TABLE1'[LOT)&&'TABLE1'[DEVICE]=EARLIER('TABLE1'[DEVICE])))
Now don't ask me how EARLIER() works. Just learn how to use it and enjoy the awesomeness. Just assume the mothership in Redmond got it right.
Hey,
to explain the working of the EARLIER() function, one has to start with this: The name of function is totally misleading, a better name would be OuterContext.
We are starting with a ROWCONTEXT, this is simply because we are creating a calculated column, CALCULATE adds a FITERCONTEXT ... Now there is a nested CONTEXT, in this case ROWCONTEXT(... FILTERCONTEXT(... )) each we want to access values from the outer context we have to use EARLIER().
In my opinion the usage of variables creates much more understandable DAX statements.
Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |