Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi, I need to know how to get the lowest ShippingPrice and the highest ShippingPrice for a ProductID. Thanks
ProductID | ShippingPrice | Lowest Price | Highest Price |
5 | 32.38 | ||
6 | 11.61 | ||
4 | 65.83 | ||
3 | 41.34 | ||
4 | 51.30 | ||
3 | 58.17 | ||
5 | 22.98 | ||
9 | 148.33 | ||
3 | 13.97 | ||
4 | 81.91 | ||
1 | 140.51 | ||
4 | 3.25 | ||
4 | 55.09 | ||
4 | 3.05 | ||
8 | 48.29 | ||
9 | 146.06 | ||
6 | 3.67 | ||
2 | 55.28 | ||
3 | 25.73 | ||
4 | 208.58 | ||
8 | 66.29 | ||
5 | 4.56 | ||
1 | 136.54 | ||
6 | 4.54 |
Solved! Go to Solution.
Hi @iamprajot,
In addition, you can also create measures or calculated columns use ALLEXCEPT Function below:
Lowest Price = CALCULATE(MIN('Table2'[ShippingPrice]),ALLEXCEPT(Table2,'Table2'[ProductID]))
Highest Price = CALCULATE(MAX('Table2'[ShippingPrice]),ALLEXCEPT(Table2,'Table2'[ProductID]))
Best Regards,
Qiuyun Yu
Hi @iamprajot,
In addition, you can also create measures or calculated columns use ALLEXCEPT Function below:
Lowest Price = CALCULATE(MIN('Table2'[ShippingPrice]),ALLEXCEPT(Table2,'Table2'[ProductID]))
Highest Price = CALCULATE(MAX('Table2'[ShippingPrice]),ALLEXCEPT(Table2,'Table2'[ProductID]))
Best Regards,
Qiuyun Yu
Much appreciated, thanks.
This is perfect use for the EARLIER function.
Lowest Price = CALCULATE( MIN([Shipping Price]),
FILTER(Table, [ProductID] = EARLIER([ProductID]))
)
And similar, you can switch MIN to MAX for the Highest Price for that Product.
The FILTER component will return a table of similar ProductIDs to the row being calculated on, and then the MIN function will return the lowest shipping price for this filtered table.
Unfortunatly it shows an error and highlights the Red part
EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
Lowest Price = CALCULATE( MIN([Shipping Price]),
FILTER(Table, [ProductID] = EARLIER([ProductID]))
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |