Forum Discussion

maashi's avatar
maashi
Frequent Visitor
2 years ago
Solved

Vlookup, multiple filters

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])

 
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.
 
'Need help'
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      maashi
      Frequent Visitor
      2 years ago
      Thank you so much, that worked seamlessly. Much appreciated. Marcy