Forum Discussion
First Time Customer List
Hello Everyone,
I have a column chart where I show a count of new customers by month with "month year" along the x-axis.
Similarly, I would like to create a matrix/list that shows the name of the new customers in each month (as opposed to count).
Below is an image of what I have already.
I've done a search of the community, but not had much luck with the keywords.
Any tips,
Thanks
Rich
- 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.
11 Replies
Anonymous
Rich, Happy to help. Could you post some sample data instead of screenshot?
What happened when you tried with matrix or table visual?
I'm not sure the best way to share the data. I'll have to come up with a way to hide some of the customer's details...
In the meantime, to answer your other question... when I try a matrix I seem to get all of the orders in the month. I know with certainty that some of our customers order every month, usually multiple times, but when I click on any column in the chart to cross filter the list and see the "new customers" that month, the regular customers will appear along with the new customers. Further frustrating me, is that the same customer will show up multiple times in the same month.
My count by month of first time orders is done using a DAX calculation, I'm not sure if that matters. Seems to work in the chart but not in a table.
Thanks
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,
Tom
