How to filter complex result

[rainchong7401]

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 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.

[v-jianboli-msft · Accepted solution]

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.

[Mahesh0016]

Hey @rainchong7401 
would do you want to create 3 table or calculated Column in table Using mention your query ?

[MFelix]

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]

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]

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]

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]

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. 

[Mahesh0016]

@rainchong7401 
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.