topic Re: Why is my measure not evaluating to 1? in DAX Commands and Tips

Why is my measure not evaluating to 1?

VBAmazing — Fri, 11 Oct 2024 19:55:40 GMT

Hello Forum,

I have a series of Measures I am trying to use to identify the first instance of a Customer placing an Order.

I need to accomplish three things:

Identify the Minimum Order Date across a series of dimensions
Return "1" for rows where the order falls within the date context
Sum all rows where "1" was returned

I am able to accomplish one and two, but my third Measure is giving unexpected results. Why are the highlighted values appearing as blank?

__NewAccounts_A_FirstOrderDate:

__NewAccounts_A_FirstOrderDate = var maxContextDate = MAX( Dates[Date] ) RETURN CALCULATE( MIN( Sales[Delivery Date] ), ALLEXCEPT( 'Sales' , Customer[Customer No.], --Customer[Customer], Supplier[Supplier], Location[Branch] ), Sales[DeliveryDate] >= DATE( YEAR( maxContextDate ) - 1, 1, 1), Sales[Delivery Quantity] > 0 )

__NewAccounts_B_FirstOrderInContext:

__NewAccounts_B_FirstOrderInContext = var maxContextDate = MAX( Dates[Date] ) return IF( [__NewAccounts_A_FirstOrderDate] >= DATE( YEAR( maxContextDate ), 1, 1) && [__NewAccounts_A_FirstOrderDate] <= maxContextDate, 1, 0 )

__NewAccounts_C_DistinctNewCustomers:

__NewAccounts_C_DistinctNewCustomers = CALCULATE( DISTINCTCOUNT( Customer[Customer No.] ), FILTER( Sales, [__NewAccounts_B_FirstOrderInContext] = 1 ) )

Re: Why is my measure not evaluating to 1?

Dangar332 — Fri, 11 Oct 2024 20:04:59 GMT

Hi, VBAmazing

Can you provide some sample Data and clarify your scenario in detail.

Re: Why is my measure not evaluating to 1?

VBAmazing — Fri, 11 Oct 2024 20:17:12 GMT

Dangar332 Thank you for your reply. The data is sensitive and unfortunately cannot be shared in a .PBIX, though that would be ideal.

However the scenario is:

I need to identify Customers who placed their first order within one year of the Maximum Context Date
I have a measure that correctly identifies the minimum Order Date by Customer (and a few other dimensions)
I have another measure that identifies whether that minimum date falls in the desired date context

Now I need to be able to Count the Distinct Customer IDs where the minimum date falls in the context.

Currently - as in my screenshot above - You can see those measures in action.

However, I am only able to count the distinct Customer IDs in some of my rows.

Based on my current understanding of the logic, I should be getting a 1 in each __NewAccounts_C_DistinctNewCustomers row where __NewAccounts_B_FirstOrderInContext is 1, but that's not the case.

Re: Why is my measure not evaluating to 1?

Anonymous — Mon, 14 Oct 2024 02:50:24 GMT

Hi ALL,
Firstly Dangar332 thank you for your solution!
And VBAmazing ,I tried to use your code to run it in the example data I created but he seems to be running fine, since you can't show your example data, can you reproduce your problem based on the pbix file I provided.

You can contact me anytime and I will get back to you as soon as I receive your message, looking forward to hearing from you!