DAX measure to calculate frequency of volume over max capacity

[Leesanity]

Hi community,

I am trying to build a Measure in my report to calculate the frequency of delivered volume over the max capacity. I wrote a DAX which is not working as expected and cannot figure where did I do wrong. Can someone help me check where I need to adjust?

Table with needed 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

Based on the available data, I want to build a matrix like below 

Store	Times of a week delivered carton qty exceed Max capacity
A	3
B	0

Delivered qty exceed Max capa = 
Var Daily_summary =
     ADDCOLUMNS(
        SUMMARIZE( 
    '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.15,1,0)
    )
RETURN
    CALCULATE(
        COUNTAX(
            Daily_summary,[Delivered qty vs Max Rece Capa]=1
            ),
            USERELATIONSHIP('Rolling Calendar'[Date],'Fact table'[Delivered date])

    )

[Leesanity]

Hi @v-binbinyu-msft,

Have a follow-up question: using the code above, I generate a matrix like below:

However, this matrix didn't return right outcome. I created a table using DAX for Daily_summary to check the result accuracy. Then I found that there should be 3 times when the delivered qty exceed max capacity but the matrix only shows 2. Meanwhile it didn't show any figures when I added the 'Rolling Calendar'[Date] into the matrix.

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

Cheers,
Leesanity

[v-binbinyu-msft · Accepted solution]

Hi @Leesanity ,

1. Context and Relationships: Ensure that the relationship between your tables ('Fact table', 'Dimension table', and 'Rolling Calendar') is correctly set up in your model. Since you're using 'USERELATIONSHIP' to activate an inactive relationship for the calculation, double-check that this relationship is correctly defined.

2. Measure Calculation: In your 'IF' statement within the 'ADDCOLUMNS' function, you're comparing '[Delivered qty]' to '[Max Rece Capa]*1.15'. This part seems correct, but ensure that the 'Max Rece Capa' is correctly pulling the maximum capacity from your 'Dimension table'. If 'Max Rece Capa' is not calculated as expected, it could lead to incorrect comparisons.

3. COUNTAX Usage: The 'COUNTAX' function expects a table as its first argument and an expression that evaluates to true or false for each row of the table as its second argument. However, the expression '[Delivered qty vs Max Rece Capa]=1' might not be evaluated as expected within the 'COUNTAX' function. Instead, consider using 'SUMX' over 'Daily_summary' and directly summing the 'Delivered qty vs Max Rece Capa' column, which already contains 1s and 0s based on your condition.

Var Daily_summary =
     ADDCOLUMNS(
        SUMMARIZE( 
            '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.15,1,0)
    )
RETURN
    CALCULATE(
        SUMX(
            Daily_summary,[Delivered qty vs Max Rece Capa]
        ),
        USERELATIONSHIP('Rolling Calendar'[Date],'Fact table'[Delivered date])
    )

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Leesanity]

Thanks @v-binbinyu-msft for the quick response! It works fine now.😉