Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Hoping someone can help me add a calculated column that ranks or counts occurrence number of a Product Id within a Region based on the order number.
Where there are multiple instances of the Product Id sold within a Region, then the earliest Order is ranked or counted 1st.
Thanks.
Region | Product Id | Order | Sales | Expected Value |
North | 901 | 100001 | 100 | 1 |
North | 901 | 100002 | 100 | 2 |
North | 902 | 100003 | 120 | 1 |
North | 902 | 100004 | 120 | 2 |
North | 902 | 100005 | 120 | 3 |
West | 901 | 100006 | 100 | 1 |
West | 902 | 100007 | 120 | 1 |
West | 902 | 100008 | 120 | 2 |
West | 903 | 100009 | 130 | 1 |
West | 903 | 100010 | 130 | 2 |
East | 901 | 100011 | 100 | 1 |
East | 901 | 100012 | 100 | 2 |
East | 901 | 100013 | 100 | 3 |
East | 902 | 100014 | 120 | 1 |
East | 903 | 100015 | 130 | 1 |
East | 903 | 100016 | 130 | 2 |
South | 901 | 100017 | 100 | 1 |
South | 901 | 100018 | 100 | 2 |
South | 901 | 100019 | 100 | 3 |
South | 901 | 100020 | 100 | 4 |
South | 901 | 100021 | 100 | 5 |
South | 903 | 100022 | 130 | 1 |
Solved! Go to Solution.
Create new column as
column = CALCULATE(count(Table1[Order]);FILTER(Table1;Table1[Region]=EARLIER(Table1[Region])&&Table1[Product Id]=EARLIER(Table1[Product Id])&&Table1[Order]<=EARLIER(Table1[Order])))
I hope it works for you
Create new column as
column = CALCULATE(count(Table1[Order]);FILTER(Table1;Table1[Region]=EARLIER(Table1[Region])&&Table1[Product Id]=EARLIER(Table1[Product Id])&&Table1[Order]<=EARLIER(Table1[Order])))
I hope it works for you
Thank you alsasloren.
That is awesome it worked perfectly!
Can the formula also be modified as a measure?
Thanks again alsasloren for the quick response.
I wasn't able to get this one to work. I changed each of the Table1[Column] references to Region, Poduct Id and Order.
Do I need to have these inside another function?
Hi again I forgot to declare column content.
Actually it wasn't so important but i have to create it. just create new column and give a value as 1
Like;
column = 1