Forum Discussion
Select Max Values by Category within an Filter
Hello,
first of all to the circumstances I´ve got right now:
I have a sql database witch record the movement of some matiraial throg a automatic production line.
Now I want to know how many materials have been at any station at a givin time.
What I´ve done so far is, to calculate the count of materials which passed or stay at a station in a difined timespan.
My problem right now is that I have no idea how I get rid of the passed materials, because I only want that ones which curently stay in that position (the newest movement-time).
CylMoveCylID - CylMoveCreated - CylMoveDestination
Test_2 = CALCULATE(
COUNT('KW CylMove'[CylMoveCylID]);
FILTER(ALL('KW CylMove');'KW CylMove'[CylMoveCreated] > SELECTEDVALUE('KW CylMove'[CylMoveCreated]) -2 && 'KW CylMove'[CylMoveCreated] < SELECTEDVALUE('KW CylMove'[CylMoveCreated]));
FILTER(ALL('KW CylMove');('KW CylMove'[CylMoveDestination]=281 || 'KW CylMove'[CylMoveDestination]=282...);
)
This is the result of my query. In that example I would only like to see the newest movement of Material 20496.
How can I add this filter to my query?
Thanks in advance,
Nicolas
Hi merkelni
You may try to use RANKX Function as below:
Measure =
VAR sort =
RANKX (
FILTER (
ALL ( 'KW CylMove' ),
'KW CylMove'[CylMoveCylID] = MAX ( 'KW CylMove'[CylMoveCylID] )
&& 'KW CylMove'[CylMoveDestination] = MAX ( 'KW CylMove'[CylMoveDestination] )
),
CALCULATE ( MAX ( 'KW CylMove'[CylMoveCreated] ) ),
,
ASC
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'KW CylMove'[CylMoveCylID] ),
FILTER ( 'KW CylMove', sort = 2 )
)Regards,
Cherie
1 Reply
- v-cherch-msft
Microsoft Employee
Hi merkelni
You may try to use RANKX Function as below:
Measure =
VAR sort =
RANKX (
FILTER (
ALL ( 'KW CylMove' ),
'KW CylMove'[CylMoveCylID] = MAX ( 'KW CylMove'[CylMoveCylID] )
&& 'KW CylMove'[CylMoveDestination] = MAX ( 'KW CylMove'[CylMoveDestination] )
),
CALCULATE ( MAX ( 'KW CylMove'[CylMoveCreated] ) ),
,
ASC
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'KW CylMove'[CylMoveCylID] ),
FILTER ( 'KW CylMove', sort = 2 )
)Regards,
Cherie