Forum Discussion

merkelni's avatar
merkelni
Regular Visitor
8 years ago
Solved

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's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft 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