Forum Discussion
First Time Customer List
- Hey,
yes it could be done, but some for effort is necessary ...
EXCEPT returns a table, instead of using this as the 1st argument of concatenatex store this into a variable.
Then use something like this
CONCATENATEX(
FILTER(ALL('<customertable>')
, '<customertable>'[customer key] IN tablevariable
)
, concatenate all the column values
, new line separator
)
Hopefully this is what you are looking for.
Regards,
Tom Hi Anonymous ,
Please try to change the comma to "IN" or other operators.
The syntax of Filter is like below. We need to write a Boolean expression for <filter>. You could reference the document to learn more.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Anonymous ,
you can achieve what you are looking for by creating a measure like so:
List of New Customer =
var theseCustomers = VALUES('Fact Sale'[Customer Key])
var CurrentMinDate = MIN('Dimension Date'[DATE])
var theseCustomerBeforeMinDate =
CALCULATETABLE(VALUES('Fact Sale'[Customer Key]),FILTER(ALL('Dimension Date'[Date]),'Dimension Date'[RunningMonthIndex] < CurrentMinDate),ALL('Dimension Date'))
return
CONCATENATEX(
EXCEPT(theseCustomers,theseCustomerBeforeMinDate )
, 'Fact Sale'[Customer Key]
, " | "
)
- The variable theseCustomers contains the current customers
- the minimum date is determined
- then all the customers are determined that bought something before the min date
- the customers that are not in the "table" theseCustomerBeforeMinDate have to be the new customers, this is determined by using the function except
- CONCATENATEX is used to create a string where the new customers are separated by " | "
Hopefully, this is what you are looking for. If not, please create a pbix file that contains sample data, but still reflects your data model, upload the pbix to onedrive or dropbox and share the link.
Regards,
Tom
I appreciate the response, but I'm struggling with the DAX provided. I keep getting errors around the month index.
Below is the DAX I am trying to use after changing some of the formula to match my tables, column...
List of New Customer =
var theseCustomers = VALUES(Invoices[Customer])
var CurrentMinDate = MIN(CalendarDate[Date])
var theseCustomerBeforeMinDate =
CALCULATETABLE(VALUES(Invoices[Customer]),FILTER(ALL(CalendarDate[Date]),CalendarDate[MonthIndex] < CurrentMinDate),ALL(CalendarDate))
return
CONCATENATEX(
EXCEPT(theseCustomers,theseCustomerBeforeMinDate )
, Invoices[Customer]
, " | "
)
The error I am seeing now is around the "monthindex"...
All the best,
Rich
- Hey,
please excuse.
Just replace the reference to the column MonthIndex with the Date column from the Calendar table.
Regards,
TomHi Tom,
The DAX you provided worked as described. I changed the separator you suggested to be a line break and it now appears like a list.
This leads to my next question though, would it be possible to use similar DAX so that I can add additional information? Such as customer name and customer country?
I tried a new column, that concatenated the customer name and customer country on the customer master sheet, and then adding it into the DAX you suggested, but it will not allow me to do that.
Any thoughts?
- Hey,
yes it could be done, but some for effort is necessary ...
EXCEPT returns a table, instead of using this as the 1st argument of concatenatex store this into a variable.
Then use something like this
CONCATENATEX(
FILTER(ALL('<customertable>')
, '<customertable>'[customer key] IN tablevariable
)
, concatenate all the column values
, new line separator
)
Hopefully this is what you are looking for.
Regards,
Tom
I corrected the formula . But Please find a new different approch at the end
//date getting compared with Month, Moved to Date List of New Customer = var theseCustomers = VALUES(Invoices[Customer]) var CurrentMinDate = MIN(CalendarDate[Date]) var theseCustomerBeforeMinDate = CALCULATETABLE(VALUES(Invoices[Customer]),FILTER(ALL(CalendarDate[Date]),CalendarDate[Date] < CurrentMinDate),ALL(CalendarDate)) return CONCATENATEX( EXCEPT(theseCustomers,theseCustomerBeforeMinDate ) , Invoices[Customer] , " | " ) //calculated month index List of New Customer = var theseCustomers = VALUES(Invoices[Customer]) var CurrentMinDate = mixx(filter(CalendarDate,CalendarDate[Date]<=MIN(CalendarDate[Date])),CalendarDate[MonthIndex]) var theseCustomerBeforeMinDate = CALCULATETABLE(VALUES(Invoices[Customer]),FILTER(ALL(CalendarDate[Date]),CalendarDate[MonthIndex] < CurrentMinDate),ALL(CalendarDate)) return CONCATENATEX( EXCEPT(theseCustomers,theseCustomerBeforeMinDate ) , Invoices[Customer] , " | " )New column approch. In this we find the min date for that customer and give a value one or customer id. Now you can use this field to get new customer.
//new column Is first Purchase = if(Invoices[Invoice date] = minx(filter(Invoices,Invoices[Customer]=earlier(Invoices[Customer])),Invoices[Invoice date]),1,0)
