Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

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

@George1973 welcome 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors