Lookupvalue error with different values in columns Search_value/Search_column

[Rashidb84]

Hi,

I've started looking into using Powerpivot/Powerquery/DAX in Excel 2 weeks ago approx.

To start off I decided to basically recreate a existing Excel report using these tools. As if I get this working it will be more efficient.

(I need this report in Excel as it well need to be forwarded and feedback inserted)

I'm currently trying to translate the following Excel formula into a DAX formula:

=INDEX('ME80FN PO Hist'!$A:$A&"/"&'ME80FN PO Hist'!$B:$B,MATCH('All Data'!G77,'ME80FN PO Hist'!$C:$C,0))

As I'm completely new I tried to do this step by step. I tried to get the first part of the data the PO number which worked with the Lookupvalue formula:

=lookupvalue(ME80FN_hist[Purchasing Document],ME80FN_hist[Material Document],Workflow_workload[Invoice Document No.])

I get an error when I tryto get the second bit of data. The PO line item:

=lookupvalue(ME80FN_hist[Purchasing Document],ME80FN_hist[Material Document],Workflow_workload[Invoice Document No.])&"/"&lookupvalue(ME80FN_hist[Item],ME80FN_hist[Material Document],Workflow_workload[Invoice Document No.])

Workflow_workload[Invoice Document No.] --> not all cells in column are unique

ME80FN_hist[Material Document]--> not all cells in column are unique

ME80FN_hist[Purchasing Document]--> not all cells in column are unique

ME80FN_hist[Item]--> not all cells in column are unique

In Power query I've changed the data type to text for all columns by the way. Don;t understand why the formula works when only the PO number is returned (even though there are duplicates here as well), but doesn't when I add the bit for the line item. How can I make this work?

I basically expect it to retun the first matching result in the table.

I’ve also applied the following formula from a Youtube (MiTutorials) video applied to my situation but this gave an error as well:

On the ME80FN_hist table I concatenated the Purchase document and Item columns for this one.

=calculate(firstnonblank(ME80FN_hist[Purch.Doc.Item],1),filter(all(ME80FN_hist[Purch.Doc.Item]),Workflow_workload[Invoice Document No.]=ME80FN_hist[Material Document]))

Out of ideas at the moment.

[Rashidb84 · Accepted solution]

Hi Guys,

Just played around with the last formula I had mentioned above and got the expected result (compared the result with data of two weeks in Excel with a positive result).

=calculate(firstnonblank(ME80FN_hist[Purch.Doc.Item],1),filter(ME80FN_hist,Workflow_workload[Invoice Document No.]=ME80FN_hist[Material Document]))

I've basically removed: all(ME80FN_hist[Purch.Doc.Item]) and just referred to the table as that is what the formula parameters suggested. Not sure what the goal of the all function was. Will dig into it when I come across it again.

Hopefully this helps someone on their learning journey :).