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 need some help as beginner,
I have 1.5 million orderlines and around 8000 individual materials (product).
Per day the same material is being ordered multiple times.
I would like to know per day what the max is that a material is ordered per productgroup.
So for example that on Monday 5 october the max for the same material in category 'T-shirts' have been ordered 186 times.
I want to show this in a clustered column chart, with selection of dates on X, max number of same material ordered on Y and category as legend.
I have a fact table with orderlines.
Per orderline I have: "material_ID", "order_date" (and more)
In a dimension table I have material information: "material_ID", "product_category".
How can I approach this?
Solved! Go to Solution.
Hi @RVL ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @RVL ,
Please refer to my .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@RVL , Create a rank like this and filter on 1
Measure =
var _tab = AddColumns(summarize(Material, material[product_category], orderlines[orderDate],"_1",sum(orderlines[Qty]))
return
rankx(filter(_tab,[orderDate] =max([orderDate])),[_1],,desc,dense)
I have not tested this
Orderlines and product_category are not in 1 same table. I think that is needed for your solution correct?
summarize we can take columns from related tables. I have shown that from different tables
Could you show an example and desired result?
If this is the data:
Material_ID | Order date | Category |
11111 | 2-10-2020 | T-shirt |
11111 | 2-10-2020 | T-shirt |
11111 | 2-10-2020 | T-shirt |
11111 | 2-10-2020 | T-shirt |
11111 | 2-10-2020 | T-shirt |
22222 | 2-10-2020 | T-shirt |
33333 | 2-10-2020 | Shorts |
55555 | 2-10-2020 | Shorts |
I would want to see that on 2-10-2020 the max that the same T-Shirt is ordered is 5 and for Short this is 1.
Thanks
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 |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
65 | |
55 |