Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to 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
@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.
Hi @SpartaBI ,
Thanks for yourv suggestion.
Here are a problem with your formula:
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
Yes!
Now it works!
Thanks a lot!!!
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 🙂
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |