Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago
    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
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 
amitchandak's avatar
amitchandak
Super User
6 years ago

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)