One Product, two suppliers - Latest Vendor Code

[George1973]

Hi All,

I have an issue, with this following table and formula:

There is one product having two difference supplier. For the purpose of the further filtering, I need to seperate them, so I have ranked the  suplliers and it works (Column "Supply Rank"), now I want to have in "Supplier Final Code" the highest ranking vendor code - In this case "772"..

The measure I created, have circular reference and does not work.

Please help..

Thanks in advance,

[SpartaBI]

@George1973 write this:

Supplier Final Code = 
VAR _current_kod = 'Supplier and Goods'[Goods_KOD]
VAR _result = 
	CALCULATE(
		MAX('Supplier and Goods'[Supplier_LookUP),
		REMOVEFILTERS('Supplier and Goods'),
		'Supplier and Goods'[Goods_KOD] = _current_kod
	)
RETURN
	_result

Not sure we you need the rank column for the max value of supplier lookup.
If it's just the highest number you can delete the rank column. I didn't use it in my function.

Showcase Report – Contoso By SpartaBI

     

[George1973]

Hi @SpartaBI ,

Thanks for yourv suggestion.
Here are a problem with your formula:

MAX('Supplier and Goods'[Supplier_LookUp]) - Won't work 😞

This approach is only accaptable for this specific Item and note that, the column is TEXT format.
So, I would ask you to help in getting "Supplier_LookUp" value with the MAX of "Supplier Rank" 

Thanks again in advance,

[SpartaBI · Accepted solution]

@George1973 ok, try this:

Supplier Final Code = 
VAR _current_kod = 'Supplier and Goods'[Goods_KOD]
VAR _max_rank_for_this_supplier = 
	CALCULATE(
		MAX('Supplier and Goods'[Supplier_rank),
		REMOVEFILTERS('Supplier and Goods'),
		'Supplier and Goods'[Goods_KOD] = _current_kod
	)
VAR _result = 
	CALCULATE(
		SELECTEDVALUE(('Supplier and Goods'[Supplier_LookUP),
		REMOVEFILTERS('Supplier and Goods'),
		'Supplier and Goods'[Goods_KOD] = _current_kod,
		'Supplier and Goods'[Supply Rank] = _max_rank_for_this_supplier
	)
RETURN
	_result
		

Showcase Report – Contoso By SpartaBI

     

[George1973]

Yes!
Now it works!

Thanks a lot!!!

[SpartaBI]

@George1973 my pleasue 🙂
Give it a thumbs up 🙂

[George1973]

You do not know for how long I was fighting with that problem!
While filtering products bu suppliers, in case of having 2 and more vendors for one SKU, I had a real headake 🙂
Not, thanks to you.. It's solved 🙂

[SpartaBI]

@George1973 welcome 🙂