Forum Discussion

javedbh's avatar
javedbh
Helper II
9 years ago
Solved

Calculate Min/Max and Count

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...
Need Help
Show and Tell
  • TheOckieMofo's avatar
    TheOckieMofo
    9 years ago

    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's avatar
TomMartens
Super User
9 years ago

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