Vlookup, multiple filters

[maashi]

I have been trying to replicate something I had been doing very manually on Excel, involving a VLOOKUP then counting non blanks in the result. I have attached the sample data for that purpose, and this is the working to replicate it in Power BI.

Working Column =

var _order = 'Table2_2'[Original Sales Order]

Return

MAXX(FILTER('Table2_2','Table2_2'[Sales Order No]=_order),'Table2_2'[Sales Order No])

New Measure :

NCP on NCP = DISTINCTCOUNT('Table2_2'[Working Column])

Sample Data

But then I realised, the sample data has already been filtered out once. How do I add in another filter so I only need to use one working column (If possible)?

Basically, in the return part of the working column, as well as looking up the 'original order number' in earlier lines to see if it has already appeared, it also needs to look up to see if order type of the earlier order number matches one of two specific  types. I tried using lookupvalue to create a second working column (to look up the first working column result to see if it is the correct sales order type) and the result was just a mess, because each sales order can appear multiple times (although in the real original data there is another identifier column to show what the line number is in each sales order number).

I have added the sales order type in the sample data column, have made it all one type (because that is correct for this data) but what if there are other sales order types? And I don't want those included in the maxx filter? I guess to summarise, how do i put in a second filter into the maxx function so that not only is it looking for the var to have appeared earlier in a different column, it also looks to make sure the sales order type is correct?

Hope this explanation is clear enough and thanks for the help.

[Anonymous · Accepted solution]

Hi @maashi ,

I can not see the sales order type in the sample data.

Based on the description, try to use the following dax formula.

Working Column =
VAR _order = 'Table2_2'[Original Sales Order]
RETURN
    MAXX(
        FILTER(
            'Table2_2',
            'Table2_2'[Sales Order No] = _order &&
            'Table2_2'[Sales Order Type] IN {"Type1", "Type2"}
        ),
        'Table2_2'[Sales Order No]
    )

Best Regards,

Wisdom Wu

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

[maashi]

Thank you so much, that worked seamlessly. Much appreciated. Marcy