Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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))
User | Count |
---|---|
85 | |
78 | |
70 | |
49 | |
41 |
User | Count |
---|---|
111 | |
56 | |
50 | |
42 | |
40 |