Create a calculate column consist of Prospective Customers for a product

[Ritesh_Air]

Hi,

I am looking for prospects who didn't buy certain products. Like here: I have these customers who bought certain products (A, B, C etc...).

I am looking for something like this:

Customer 1 is only prospect for Product E,

Customer 2 is prospect for everything except A and C....so on and so forth.

I had a solution with measures but client is looking for calculated column so everything in one table. How do I achieve this? I have uploaded the pbix.

https://drive.google.com/file/d/16UfeCTNx-8Ak-J2zPGeG80g3QsboBNbM/view?usp=sharing

Thanks,

-rk

[lbendlin · Accepted solution]

Here's a solution that uses Power Query instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDA6VYHQjfCYiNkPjOaHwXJL4Rmn4jqHpjKN8YTd4Yaj4y3xXGjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t, CY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CY", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Product]), "Product", "CY"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A","B","C","D","E"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Customer"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 0))
in
    #"Filtered Rows"

[v-lionel-msft · Accepted solution]

Hi @Ritesh_Air ,

Based on @lbendlin ‘s solution, you can  create a calculated column.

Best regards,
Lionel Chen

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

[lbendlin · Accepted solution]

Here's a solution that uses Power Query instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDA6VYHQjfCYiNkPjOaHwXJL4Rmn4jqHpjKN8YTd4Yaj4y3xXGjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t, CY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CY", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Product]), "Product", "CY"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A","B","C","D","E"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Customer"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 0))
in
    #"Filtered Rows"