what is the difference between these two dax calculated columns

[CL7777]

These give different answers in my model and I do not understand why. Can someone explain to me the difference? does it have to do with the context transition of Calculate? I have a very simple model in which sales is connected to date as a many to one relationship. Below CODE #2 gives the right answer and CODE #1 gives the wrong answer.

CODE # 1

deliverydays=

CALCULATE (COUNTROWS ( 'Date' ),

FILTER (

ALL ( 'Date' ),

'Date'[Date] > Sales[Order Date]

&& 'Date'[Date] < Sales[Delivery Date]

&& 'Date'[Working Day] = "WorkDay" 

        )

)

CODE # 2

DeliveryDays =
VAR OrderDate = Sales[Order Date]
VAR DeliveryDate = Sales[Delivery Date]
VAR WorkingDays = FILTER (ALL ( 'Date' ),
'Date'[Date] >= OrderDate
&& 'Date'[Date] <= DeliveryDate
&& 'Date'[Working Day] = "WorkDay"
)
VAR CountWorkingDays = COUNTROWS ( WorkingDays )
RETURN CountWorkingDays

[v-frfei-msft · Accepted solution]

Hi @CL7777 ,

I have made a sample for your reference, the result should be the same using the two formulas. Please not we should use >= and <= instead of > , < .

deliverydays = 
CALCULATE (
    COUNTROWS ( 'Date' ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] >= Sales[Order Date]
            && 'Date'[Date] <= Sales[Delivery Date]
            && 'Date'[Working Day] = "WorkDay"
    )
)

DeliveryDays_ = 
VAR OrderDate = Sales[Order Date]
VAR DeliveryDate = Sales[Delivery Date]
VAR WorkingDays =
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] >= OrderDate
            && 'Date'[Date] <= DeliveryDate
            && 'Date'[Working Day] = "WorkDay"
    )
VAR CountWorkingDays =
    COUNTROWS ( WorkingDays )
RETURN
    CountWorkingDays

Also attached the pbix .

[VasTg]

@CL7777 

I guess you are creating the column in Sales table and i believe its the context transition as you said which is filtering the data. Try countx instead of calculate to verify that.