Need to do a lookup value from 1 table and return a value from a match in a 2nd table (many to one)

[Freakaholic]

Trying to take a SAP report and visualize it in PBI.

Table 1: Each line show the source bin of a material pick ( relationship MANY )

Table 2: Each source bin is here unique (column 1) and has a point value (column 2) ( relationship ONE )

I manage to set op a report that shows the points for all picks as long as the picks are from unique sources, if a pick is from the same source 2 or more times only this first is counted.

How can i set this up to work??

Table 1 example:

TO Number	Material	Typ	Source Bin	Typ	Dest. Bin	Dest.target qty	AUn	Created On	User	Conf-date	User	Conf.t.
1257763	1053793	2	P07-C4-01	921	TRANSFER	100	M	16.03.2016	COP	16-03-2016	COP	09:07:44
1257827	1018034	13	P07-A1-01	921	TRANSFER	100	M	16.03.2016	COP	16-03-2016	COP	10:44:02
1256951	1014669	2	P07-C4-01	921	TRANSFER	48,7	M	15.03.2016	COP	15-03-2016	COP	11:02:20

Table 2 example:

Source label	Points
P07-C4-01	2
P07-A1-01	3

Result returned should be 7, but I can only get it to show 5

[Sean]

@Freakaholic Can you explain how you come up  with 7?

So you are looking to

multiply for each Source label in Table 2 => the Point Value from Table 2 * the Count of all related source bins

Is this is how you come up with 7

Source Label => Point Value * COUNT of Source bIn in Table 1

P07-C4-01 =>  2 * 2 => 4
P07-A1-01 =>  3 * 1 => 3

[Freakaholic]

Hi Sean,

7 is reached as you describe:

a pick in P07-C4-01 is worth 2 point - so two picks equals 4 points

a pick in P07-A1-01 is worth 3 point - so one pick equals 3 points

Giving a total of 7 points

I do this in excel today with a VLOOKUP that returns the result that I then sum up

I can get PBI to do the magic as long as there isn't more than 1 pick in each source, if i have more identical pick from same source it will only count the first

[Sean]

@Freakaholic I got it working with calculated columns - see picture...

Partially works with Measures too

grand total doesn't sum the rows but multiplies total points (3+2) times (2+1)

Its really late here - will give it another try tomorrow...

[Freakaholic]

Appreciate your effort

Sleep tight