Add a column using LOOKUPVALUE but in the same table where I also want to lookup matching values

[JOAP]

Hi,

This one has been a real struggle for me.

I'm trying to add a new column in my finance facts table using DAX function LOOKUPVALUE, but the tricky thing is that I only want to look for values in that same table (finance facts). When I hardcode the #search value# as in the attached picture to "ST0151_1909AC_3001_2001_SEK" it works but that is of course not doable since it should be dynamic. Everytime I try to set #search value# to column IC_Match_Rec I get "A table of multiple values was supplied where a single value was expected". The values in column IC_Match_Rec are unique, so I cannot understand this?

Below works but #Search value# is hardcoded and should be referencing the column IC_Match_Rec but then "multiple values error"?

Grateful for any tips I can get 🙂

Best Regards,

Johan

[AlB]

Hi @JOAP 

The column you need to check for unique values is IC_Match_Pay, since it's the one the filtering is being done on. Are you taking blank values into account? 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[JOAP]

Sorry that doesn't help. In Excel a SUMIFS() or VLOOKUP() would easily fix this but I struggle in PBI Desktop.

Could maybe the SUMX() in combination with CALCULATETABLE() in PBI work?

Below didn't work:

[v-juanli-msft]

Hi @JOAP 

You could create calculated columns

Column = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))

or 

Column 2 = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]=EARLIER('Table'[IC_Match_Pay])&&'Table'[IC_Match_Pay]<>BLANK()))

Or create a measure

Measure = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JOAP]

I'll go back a bit and explain my problem again because I'm really after a quick and lean solution not causing any burden on finding the matching data on any report visual. I also don't want to insert any calculated columns that really aren't necessary.

I've created this measure to capture the internal stock value free from any filter applied:

ISTO_Stock_ST0150 = CALCULATE('Measures Amounts'[Amount_YTD];Accounts[Account_Level0]="ST0150")

I've also created this meausre to capture the internal margin free from any filter applied:

ISTO_Margin_ST0151 = CALCULATE('Measures Amounts'[Amount_YTD];Accounts[Account_Level0]="ST0151")

Then in a table visual I have made some filters to only caputure a certain period, actuality, currency etc....

The below table data is fully ok but what I want is to match what the reporting company under Company_Code has reported as internal stock bought from another company with what that reporting company has reported as margin. The thing is that I want to show that on the same row. Example below where Company_Code "2001" reported 251 as internal stock bought from IC_Counter_Company_Code = "3001" and the matching margin is then 30.00 (%) found further below reported by Company_Code = "3001" and IC_Counter_Company_Code = "2001".

So if I for example would filter this table visual only on Company_Code = "2001" then I would only get 1 Row with ISTO_Stock_ST0150 = 251 and ISTO_Margin_ST0151 = 30.00

Best Regards,

Johan