Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Fabric Community,
Can you help me with DAX below? The DAX for a measure but didn't return the expected result.
Goal result: How many overdeliveries happened across the stores in a week? I am trying to get a table like below example
Overdeliveries times | |
Week 1 | |
Store 1 | 2 |
Store 2 | 2 |
Store 3 | 1 |
Total | 5 |
Data:
1) Fact table: carton level information including destination store, promised delivery date & delivered date.
2) Dimension table: destination store and max capacity
3) Rolling calendar: 1 active relationship on promised delivery date & 1 inactive relationship on delivered date
DAX:
Count of overdeliveries =
VAR Daily_summary =
'Fact table',
'Fact table'[Destination store],
'Fact table'[Delivered date],
"Delivered qty", DISTINCTCOUNT('Fact table'[Carton ID]),
"Max Rece Capa", MAX('Dimension table'[Rec. Cap.])
),
"Delivered qty vs Max Rece Capa", IF([Delivered qty]>[Max Rece Capa],1,0),
"Week Number", WEEKNUM( 'Fact table'[Delivered date],2)
)
VAR Weekly_summary =
Groupby(
Daily_summary,
'Fact table'[Destination store],
[Week Number],
"Overdelivery",SUMX(CURRENTGROUP(),[Delivered qty vs Max Rece Capa]
))
Return
CALCULATE(
sumx(Weekly_summary, [Overdelivery]),
USERELATIONSHIP('Rolling Calendar'[Date],'Fact table'[Delivered date]
)
)
However, this matrix didn't return right outcome. I created a table using DAX to return Week_summary to check the result accuracy. Then I found that there should be 6 times when the delivered qty exceed max capacity (Store 1, 2, 3 & 4 following the sequence) on Week 7.
but the matrix only shows 2 for Store 1.
Is this something you can help me to understand why there is such a discrepency? Thanks in advance.
Cheers,
Leesanity
Hi @amitchandak ,
Thanks for the solution!
I tried it but it seems not working. The M1 is returning total sum of carton but not cartons delivered to store.
Is there any thing wrong with my DAX solution? I am thinking if it could be the modelling: the matrix is using the "Rolling_calendar"[Week Number] and "Dimension table"[Destination Store]. The Dimension table has a 1-to-many relationship with the fact table . Thanks!
Cheers,
Tian
@Leesanity , Try two measures like
M1= CALCULATE(DISTINCTCOUNT('Fact table'[Carton ID]),USERELATIONSHIP('Rolling Calendar'[Date],'Fact table'[Delivered date]))
Sumx(SUMMARIZE( 'Fact table'[Destination store],
[Week Number], "Delivered qty", [M1], "Max Rece Capa", MAX('Dimension table'[Rec. Cap.])), IF([Delivered qty]>[Max Rece Capa],1,0))
User | Count |
---|---|
85 | |
74 | |
71 | |
68 | |
56 |
User | Count |
---|---|
96 | |
94 | |
92 | |
78 | |
71 |