Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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]
, " | "
)
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
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?
Hi 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
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.
Hi Everyone,
I send good wishes in this time of Covid-19.
My apologies for the delay in responding on this tread, but I still face issues. I believe I have used the IN function properly, but I am still getting an error. Please see below and assist if possible.
Is it problem with the # of items I am trying to concatentate?
Thanks
Rich
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)
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |