@Azee , Seems that you are very interested to know how does your the oritinal DAX query really worked , Rather then finding solution for the problem to say; Even I am learning and exloring the DAX world and I am sharing my learning in the process for your and other feedback too
So Lets focus on what your ask is -That is your desire to understand the initial DAX query you mentioned .
Let me try to distill down a bit , The approach I am going to use is old Step by Step break down observe and then see it through again
-
–This is your original query
New Customer Sales (Final Step) =
–Analysis Step 1: Verify what does customer visible filter context contains
VAR Customer = VALUES(Sales[Customer ID])
Return
COUNTROWS(
FILTER(Customer,
CALCULATE( COUNTROWS(Sales) ,
–Analysis Step 2 -Verify what Does Date Filter context return for calculate to work on
FILTER(ALLSELECTED(Dates), Dates[Date] < MIN(Dates[Date]) ) ) = 0 ) )
Notice Step 1 and Step 2 comments . .
First we will see What does the Filter with All selected returns
Then we are going to see what does Visible filter context for customer number contains
Step 1 measure
New Customer Sales(Analysis Step 1) =
VAR Customer =
VALUES ( Sales[Customer ID] )
RETURN
CALCULATE (
CONCATENATEX ( VALUES ( ‘Sales’[Customer ID] ), Sales[Customer ID], ", " )
)
Step 2 measure
New Customer Sales(Analysis step 2) =
VAR Customer =
VALUES ( Sales[Customer ID] )
RETURN
CALCULATE (
CONCATENATEX ( VALUES ( 'Dates'[Date] ), Dates[Date], ", " ),
FILTER ( ALLSELECTED ( Dates ), Dates[Date] < MIN ( Dates[Date] ) )
)
–lets start debugging step by step by picking some of the dates and pull in our debug measures as below
Please read through the anotation and try to analyze thus far if you get some more visibility . .
So what really we are doing is .
- For the Selected Dates (Allselected ensures it ; if we don’t select anything ; we are essentially going through one by one each of the dates . .
Now lets say you don’t have the date column at all , Then we don’t have any reference of date , So it will display count of all the customers . . .you can try it out
Let’s move on . .
- If you observe the 2nd column in the screenshot (Via step1 analysis meausre ) -Really we are Filtering at date level (not iterating ) -what are the visible Customers
So for the 1st row . we have 12 customers
To reiterate VALUES ( Sales[Customer ID] ) returns the customers in the current visible filter context -in this case filter context contains only the date from the table column (first)
- The Date filter with all selected is really acting as a Calculate Context modifier with reference to the date in current filter context (that is actual filter context
So to say : FILTER ( ALLSELECTED ( Dates ), Dates[Date] < MIN ( Dates[Date] ) ) within the actual complete DAX query . .
Returns All dates < current filter context date .
You can clearly visualize this fact from 3rd column and also the annotation -crisply . .
-
Now move your attention to the marking for some customer number c1335 and c1004 which are repeated across some dates . .
-
if you observe the last column -All the customer number which earlier had sales were substracted from the total count (see the annotation )
So to summarize really what this formula is doing is
Iterating through customers in current visible filter context and counting no of customers which didn’t had any sales in any of the previous selected dates . .which indirectly signify that they are new customers.
Please let me know if it helps . .
