Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 .
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 .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |