Forum Discussion

rainchong7401's avatar
rainchong7401
Helper III
3 years ago
Solved

How to filter complex result

Hi Friends, I'm not able to achieve the result I want it's too difficult to get. Expected Output: Check UNITID 1st winthin the same BOMID, then only check highest BOMQTY 1. 2nd line FILTRATI...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi rainchong7401 ,

     

    Please try:

    FILTRATION_HIGHPERCENT = 
VAR _a =
    MAXX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _b =
    MINX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _c =
    (
        CONTAINS (
            FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
            'Table'[UNITID], "PAIL"
        )
            || (
                CONTAINS (
                    FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
                    'Table'[UNITID], "TIN"
                )
            )
    )
RETURN
    SWITCH ( TRUE (), _c && [BOMQTY] = _b, 1, NOT ( _c ) && [BOMQTY] = _a, 1, 0 )

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi rainchong7401 ,

 

Please try:

FILTRATION_HIGHPERCENT = 
VAR _a =
    MAXX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _b =
    MINX ( FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ), [BOMQTY] )
VAR _c =
    (
        CONTAINS (
            FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
            'Table'[UNITID], "PAIL"
        )
            || (
                CONTAINS (
                    FILTER ( 'Table', [BOMID] = EARLIER ( 'Table'[BOMID] ) ),
                    'Table'[UNITID], "TIN"
                )
            )
    )
RETURN
    SWITCH ( TRUE (), _c && [BOMQTY] = _b, 1, NOT ( _c ) && [BOMQTY] = _a, 1, 0 )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.