Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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))
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
55 | |
55 | |
37 | |
30 |
User | Count |
---|---|
78 | |
64 | |
45 | |
43 | |
40 |