Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello community,
Hope you are well.
I would appreciate your help with the below ranking problem.
Description :
I extract the shipment report and I try to calculate the number of pallets to be despatched.
This is calculated as a measure, where I divide the order quantity by the pallet quantity columns.
My data model includes dimension tables such as
Watchout :
My fact table combines all extractions, timestamped based on their extraction table.
Task :
Based on the number of pallets, I want to demonstrate the ranking per
This ranking will have to be based on the latest data extraction set.
Fact table
Extraction_Date | Product | Supplier | Market | Shipment_Date | Order_Quantity | Pallet_Qty |
01/01/2021 | Prod.1 | Supplier.1 | Market.1 | 02/01/2021 | 100 | 100 |
01/01/2021 | Prod.1 | Supplier.1 | Market.1 | 03/03/2021 | 200 | 100 |
01/01/2021 | Prod.2 | Supplier.1 | Market.2 | 15/02/2021 | 80 | 40 |
01/01/2021 | Prod.3 | Supplier.2 | Market.2 | 16/03/2021 | 30 | 30 |
01/01/2021 | Prod.4 | Supplier.3 | Market.3 | 16/02/2021 | 40 | 20 |
01/01/2021 | Prod.4 | Supplier.3 | Market.3 | 25/04/2021 | 20 | 20 |
02/03/2021 | Prod.1 | Supplier.1 | Market.1 | 03/03/2021 | 200 | 100 |
02/03/2021 | Prod.2 | Supplier.1 | Market.2 | 10/04/2021 | 120 | 40 |
02/03/2021 | Prod.3 | Supplier.2 | Market.2 | 16/03/2021 | 30 | 30 |
02/03/2021 | Prod.4 | Supplier.3 | Market.3 | 25/05/2021 | 40 | 20 |
02/03/2021 | Prod.5 | Supplier.3 | Market.3 | 12/03/2021 | 20 | 10 |
02/03/2021 | Prod.5 | Supplier.3 | Market.3 | 14/04/2021 | 10 | 10 |
Dimension tables
Supplier | Supplier_Area |
Supplier.1 | SupplierArea.1 |
Supplier.2 | SupplierArea.1 |
Supplier.3 | SupplierArea.2 |
Market | Market_Area |
Market.1 | MarketArea.1 |
Market.2 | MarketArea.2 |
Market.3 | MarketArea.3 |
Product | Status |
Prod.1 | Status1 |
Prod.2 | Status1 |
Prod.3 | Status1 |
Prod.4 | Status2 |
Prod.5 | Status2 |
Product | Category |
Prod.1 | Category1 |
Prod.2 | Category1 |
Prod.3 | Category2 |
Prod.4 | Category3 |
Prod.5 | Category4 |
Thank you in advance,
George
Solved! Go to Solution.
"I try to avoid having calculated columns if I can have a measure instead."
That is a fallacy. Remember that your ultimate goal is to provide a satisfying end user experience. Calculated columns are better than measures in that respect.
"the table key is the combo product&market" - in that case you will have to add a composite key on both sides of the relationship. You can create that in DAX, in Power Query or in your data source (preferred).
" This is calculated as a measure, where I divide the order quantity by the pallet quantity columns. "
Why is this a measure? Both values are in the same row, so a calculated column is sufficient.
What's the reason for having separate Product Status and Product Category tables? These should be combined.
Ranking means assigning positions. Do you want to rank based on who has more pallets per order or less?
Most of your requirements seems to be covered by a sort instead of a rank?
Hello @lbendlin
Thank you for your reply, please see my inputs below.
I try to avoid having calculated columns if I can have a measure instead.
The product status is a separate table because the same product can have different statuses at different markets (for example it canm be active in market 1, but run-down in market 2). Thus, the table key is the combo product&market.
Thank you,
George
"I try to avoid having calculated columns if I can have a measure instead."
That is a fallacy. Remember that your ultimate goal is to provide a satisfying end user experience. Calculated columns are better than measures in that respect.
"the table key is the combo product&market" - in that case you will have to add a composite key on both sides of the relationship. You can create that in DAX, in Power Query or in your data source (preferred).
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
97 | |
97 | |
73 | |
72 |