Calculate Min/Max and Count

[javedbh]

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??

[TheOckieMofo · Accepted 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.

[TomMartens]

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

[javedbh]

Thanks guys @TomMartens @TheOckieMofo