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.

MFelix's avatar
MFelix
Super User
3 years ago

Hi rainchong7401 ,

 

Not really getting what is the result you want to achieve, the examples you give you only have TIN and PAIL in a single row so you can get it has the value you need?

 

Try the following code:

Check = 
VAR _BOMVALUES =
    CONCATENATEX (
        FILTER ( ALL ( 'Table'[BOMID],'Table'[BOMQTY], 'Table'[UNITID] ), 'Table'[BOMID] = EARLIER ( 'Table'[BOMID] ) ),
        'Table'[BOMQTY] & 'Table'[UNITID],
        "|"
    )
RETURN
    IF (
        (
            CONTAINSSTRING ( _BOMVALUES, "1TIN" )
                && 'Table'[UNITID] = "TIN"
                && 'Table'[BOMQTY] = 1
        )
            || (
                CONTAINSSTRING ( _BOMVALUES, "1PAIL" )
                    && 'Table'[UNITID] = "PAIL"
                    && 'Table'[BOMQTY] = 1
            ),
        1,
        IF (
            CONTAINSSTRING ( _BOMVALUES, "1TIN" ) || CONTAINSSTRING ( _BOMVALUES, "1PAIL" ),
            0,
            IF (
                MAXX (
                    FILTER ( ALL ( 'Table' ), 'Table'[BOMID] = EARLIER ( 'Table'[BOMID] ) ),
                    'Table'[BOMQTY]
                ) = 'Table'[BOMQTY],
                1,
                0
            )
        )
    )

 

Has you can see below I have an additonal column with 1 and 0

 

 

I also added two new lines that have pail and tin but diferent from 1.

 

If this is not what you need can you please share some more insights on the request.

 

rainchong7401's avatar
rainchong7401
Helper III
3 years ago

Hi Felix,
I'm doing this in Data,
So, var is not usable or acceptable.
below picture seems wrong already. the output.

Or you may do like this:
Combine: 1st and 2nd condition as IF STATEMENT
Seprate: 3rd condition as another STATEMENT

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi rainchong7401 ,

     

    Why do you refer that you cannot use VAR? Do you mean that you are doing this on Power Query?

     

    The use of DAX accepts the use of VAR (variables) for the calculation bo be reused and you don't have to write it again and again.

     

    Regarding the calculation I have missread the information though there was a need for the quanity = 1 redo the calculation to:

     

    Check =
VAR _BOMVALUES =
    CONCATENATEX (
        FILTER (
            ALL ( 'Table'[BOMID], 'Table'[BOMQTY], 'Table'[UNITID] ),
            'Table'[BOMID] = EARLIER ( 'Table'[BOMID] )
        ),
        'Table'[BOMQTY],
        "|"
    )
RETURN
    IF (
        (
            CONTAINSSTRING ( _BOMVALUES, "TIN" )
                && 'Table'[UNITID] = "TIN"
        )
            || (
                CONTAINSSTRING ( _BOMVALUES, "PAIL" )
                    && 'Table'[UNITID] = "PAIL"
            ),
        1,
        IF (
            MAXX (
                FILTER ( ALL ( 'Table' ), 'Table'[BOMID] = EARLIER ( 'Table'[BOMID] ) ),
                'Table'[BOMQTY]
            ) = 'Table'[BOMQTY],
            1,
            0
        )
    )

     

    • rainchong7401's avatar
      rainchong7401
      Helper III
      3 years ago

      Hi Felix,
      Below picture is what I want to achieve.

      Expected Output: Check UNITID 1st winthin the same BOMID, then only check highest BOMQTY
      1. 2nd line FILTRATION_HIGHPERCENT return 1 is correct
          Because within the same BOMID, you do not find TIN or PAIL in UNITID. So, u get the highest      BOMQTY
      2. 6th line FILTRATION_HIGHPERCENT return 1 is correct
          Because within the same BOMID, you find TIN or PAIL in UNITID. So, u ignore the highest            BOMQTY 
      3. The rest return all 0.

      • MFelix's avatar
        MFelix
        Super User
        3 years ago

        Hi rainchong7401,

         

        Is my new formula the TINPAIL column on your example? 

         

        Can you share the formula you are using just to see if there is some adjustment that your model may need. 

         

        On my tests it appears to be correct but can have some other issue.