Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago
Solved

One Product, two suppliers - Latest Vendor Code

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's avatar
    SpartaBI
    4 years ago

    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


         

7 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      George1973
      Icon for Helper V rankHelper V
      4 years ago

      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's avatar
        SpartaBI
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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