cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
George1973
Resolver II
Resolver II

One Product, two suppliers - Latest Vendor Code

Hi All,

 

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

 

George1973_0-1652870277005.png

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,

 

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@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
		

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

@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.


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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
Community Champion
Community Champion

@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
		

 

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Yes!
Now it works!

George1973_0-1652879033860.png

Thanks a lot!!!

SpartaBI
Community Champion
Community Champion

@George1973 my pleasue 🙂
Give it a thumbs up 🙂

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
Community Champion
Community Champion

@George1973 welcome 🙂

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors