Identify New customers only on a growing system list

[Grimfandango227]

Hello, 

I have been tasked with identifying customers who have never been on this list and are new. Please see the columns below. 

So reoccurring customers are on the list, I need to identify the customers who have never had a system in the past and this is their first system.  This will be based on a monthly refresh.  

Hope this makes sense. Any help would be greatly appreciated!

Thanks

Jeremy

[v-junyant-msft · Accepted solution]

Hi @Grimfandango227 ,

@lbendlin Thanks for your reply!

And @Grimfandango227 , if you are using Power Query, I can give you several solutions:
Here is my sample data:

First solution, add an Index column for each customer_id or customer_number (Any column that identifies a different customer is ok, I'll use customer_name as an example.), here is an example:

Table.Group(#"Changed Type", {"customer_name"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

And the final output is as below:

Then you only need to look for customers with only Index=1 exists, which are new customers that have never appeared in this list before.
Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBLD4IwEIT/S89kQ7cvOHrUeNMb4VABxRcY8f/HQaDUdCf5NrOTbbYohGQlEsEpK5IkgR7a9W0nyqQQrPTiMjHwBB3752TKEMUDVnFU2RDVNGIN7X1X97PPJgwYMsAGOnyaV9tME1qZsMCSBZ6j7Xo1HTngJf6asYuZUQZs/5db68wykFMOvELbYfDVz89CXKYoNDdo857jxrrg42rj2e7R9tytcUaheYTTlF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [system_id = _t, date_created = _t, system_type_name = _t, customer_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"system_id", Int64.Type}, {"date_created", type date}, {"system_type_name", type text}, {"customer_name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"customer_name"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"system_id", "date_created", "system_type_name", "Index"}, {"system_id", "date_created", "system_type_name", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"system_id", "date_created", "system_type_name", "customer_name", "Index"})
in
    #"Reordered Columns"

Second solution, add a column to calculate the number of rows that exist for each customer. As in solution 1, select the column that identifies the unique customer. Here is an example:
This is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBLD4IwEIT/S89kQ7cvOHrUeNMb4VABxRcY8f/HQaDUdCf5NrOTbbYohGQlEsEpK5IkgR7a9W0nyqQQrPTiMjHwBB3752TKEMUDVnFU2RDVNGIN7X1X97PPJgwYMsAGOnyaV9tME1qZsMCSBZ6j7Xo1HTngJf6asYuZUQZs/5db68wykFMOvELbYfDVz89CXKYoNDdo857jxrrg42rj2e7R9tytcUaheYTTlF8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [system_id = _t, date_created = _t, system_type_name = _t, customer_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"system_id", Int64.Type}, {"date_created", type date}, {"system_type_name", type text}, {"customer_name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"customer_name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"customer_name"}, #"Changed Type", {"customer_name"}, "Solution 2", JoinKind.LeftOuter),
    #"Expanded Solution 2" = Table.ExpandTableColumn(#"Merged Queries", "Solution 2", {"system_id", "date_created", "system_type_name"}, {"system_id", "date_created", "system_type_name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Solution 2",{"system_id", "date_created", "system_type_name", "customer_name", "Count"})
in
    #"Reordered Columns"

And the final output is as below:

Then you just need to filter the rows with Count = 1 to find the corresponding customer, which are new customers that have never appeared in this list before.

You mentioned that your tables are refreshed every month, Power Query remembers the actions you take and after each refresh these steps are automatically applied to the latest data table. In other words, you don't have to perform an operation every time you refresh!

And if you need DAX, you can try to use this DAX to create a measure:

Count_Customer = 
CALCULATE(
    COUNTROWS('DAX'),
    ALLEXCEPT('DAX', 'DAX'[customer_name])
)

And put this in a table visual:

All customers whose measure is equal to 1 are new customers who have never appeared in this list. Measure also changes dynamically based on your data table, meaning you still don't have to do anything when your data is updated.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

create a table visual with customer id and minimum (first) date_created implicit measure. That will show you when each customer was first on the list. Add the date_created again, but this time as count.  A count of 1 will identify new customers and when they joined.