Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |