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.
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
please excuse.
Just replace the reference to the column MonthIndex with the Date column from the Calendar table.
Regards,
Tom
Hi 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,
TomHi Tom,
First off, I appreciate your help with this. I'm still relatively new to PBI and I am trying to learn as much as I can about it, but clearly this last question is above my skill-level. The first solution you provided works sufficiently for now.
My attempt at your latest suggestion is below. I do not know DAX well enough to troubleshoot the problem with it yet. If you could help, that would be great, but understood if not.
All the best,
Rich