Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

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



2 Replies

  • Anonymous , 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))

  • Anonymous's avatar
    Anonymous
    Not applicable

    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