Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Leesanity
Frequent Visitor

DAX measure not returning expected result

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 12
Store 22
Store 31
Total5


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.
Leesanity_0-1712329949039.png
but the matrix only shows 2 for Store 1. 

Leesanity_1-1712330082868.png


Is this something you can help me to understand why there is such a discrepency? Thanks in advance.

Cheers,

Leesanity



2 REPLIES 2
Leesanity
Frequent Visitor

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 

amitchandak
Super User
Super User

@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))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.