Using MAXX to retrieve identical values from another table

[Anonymous]

Hello  -  I have two tables, not joined in a direct relationship  (but an indirect one between the tables/columns below).  

The following two formulas, independently work fine.    If a matching item from the Forecast table is found, it returns that item as a "match" on the Ordres table.     Same for the accounts.   

However, this is providing incorrect results in my table.   I essentially need to combine these two formulas into one....so that both the matching item AND the matching account on the Forecast table, are returned to their matching equivalent on the Orders table.   Is there a way to combine these two into one formula  (or perhaps a better method)?   

Match Item from Forecast = maxx(filter(Forecasts,Forecasts[Item]= Forecasts[Item] && Sales_Orders_for_Charts[Item]= Forecasts[Item] ),Sales_Orders_for_Charts[Item])

Match Prim Customer from Forecast = maxx(filter(Forecasts,Forecasts[Account]= Forecasts[Account] && Sales_Orders_for_Charts[Primary Customer Name]= Forecasts[Account] ),Sales_Orders_for_Charts[Primary Customer Name])

[AllisonKennedy]

I'm not sure about the bold part in your formula.

Match the forecast element of the forecast version of maxx(filter(forecasts,forecasts[article], forecasts[article] && Sales_Orders_for_Charts[Article], Forecasts[article] ), Sales_Orders_for_Charts[Item])

Which tables connect these two? Is there an Item table that has each item only appears once, and an account table that has each account appears only once? If so, you would consider using them as the table in MAXX measurements - you're wanting the maximum for each item and the maximum for each account, right? Its formula is 'you strictly speaking a 'match'

You can also try the SEARCH feature, but I need a little more detail about what you're really trying to find (i.e., what is a 'match'?)

Has this post solved your problem? Please mark it as a solution for others to find you quickly and so the community knows that your problem has been resolved.

If you have found this post useful, please give Kudos.

I work as an instructor and consultant for Microsoft 365, specializing in Power BI and Power Query.

https://sites.google.com/site/allisonkennedycv

[AllisonKennedy]

I'm not sure about the part in bold in your formula.

Match Item from Forecast = maxx(filter(Forecasts,Forecasts[Item]= Forecasts[Item] && Sales_Orders_for_Charts[Item]= Forecasts[Item] ),Sales_Orders_for_Charts[Item])

What tables connect these two? Is there an Item table that has each item only listed once and an account table that has each account only listed once? If so, I would consider using those as the table in the MAXX measures - you're wanting the max for each item and the max for each account, right? Your formula isn'te strictly speaking a 'match'

You can also try the LOOKUP function, but I need a bit more detail on what you're actually trying to find (ie what is a 'match'?)

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

The Forecast and Orders tables are not directly connected, and by their nature, they have both have many repeated values  (no distinct values).     The forecast table could have the same client mentioned 20 times, but each time with a different item that is being forecasted.  

I am trying to match those items on the Forecast table, that also show up on the Orders table  (ie....they got ordered).    So again, a client on the forecast could have 30 items showing up in our forecast, but maybe only 10 get ordered.....I am trying to match each client with the items that exist both on the forecast table and the orders table   (i.e.....the items that were forecasted but also ordered).  

Does that help?