Power Query DAX

[alir22456]

I have a dataset of customers and the services (Plans) they had bought. I am working on a Cross-Selling analysis project. The data is arranged as below:

Based on this data, I was able to generate the list of the customers who (for example) bought Plan 4 and Plan 10.

Now I need a list of customers who bought Plan 10 minus the customers who bought Plan 10 and Plan 4

For example:

Targeted Customers = Plan 10 Customers - (Plan 10 Customers and Plan 4 Customers)

Thanks

[PhilipTreacy]

Hi @alir22456 

You've posted in the Power Query forum.  Power Query doesn't use DAX.

I'll move this topic to the Desktop forum where you will get a solution in DAX.

Regards

Phil

[dufoq3]

Hi @alir22456, 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS0yVNJRCshJzFMwNFCK1cEiboRd2ARF2AgmbIYibAwTNsUubIRd2Bi7sKEhirgJDpebYjfdFLvpptg9ZIrd5aaE7DRENd0Mu+lmyMbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customers = _t, Services = _t]),
    GroupedRows = Table.Group(Source, {"Customers"}, {{"Services", each List.Distinct([Services]), type table}}),
    Transformed = [ plan10 = Table.SelectRows(GroupedRows, each List.Contains([Services], "Plan 10"))[Customers],
    plan10_and_4 = Table.SelectRows(GroupedRows, each List.ContainsAll([Services], {"Plan 10", "Plan 4"}))[Customers],
    tbl = #table(type table[Services=text, Customers=text], {
        { "Plan 10", Text.Combine(plan10, ", ") },
        { "Plan 10 and Plan4", Text.Combine(plan10_and_4, ", ") },
        { "Plan 10 - (Plan10 and Plan4)", Text.Combine(List.Difference(plan10, plan10_and_4), ", ") }
      })
   ][tbl]
in
    Transformed

[alir22456]

Hi @dufoq3 

Thanks but i'm looking for a DAX Measure to calculate the list of customers. I have a slicer for the service plans. The below DAX measure returns a list of customers who had purchased the selected services in the slicer.

Customers with Selected Services = 
VAR __tmpTable1 =
GENERATE(
DISTINCT('Table'[Customers]),
EXCEPT(
DISTINCT('Table'[Services]),
CALCULATETABLE(DISTINCT('Table'[Services]))
)
)
VAR __tmpTable2 = SUMMARIZE(__tmpTable1, 'Table'[Customers])
VAR __tmpTable3 = EXCEPT(DISTINCT('Table'[Customers]), __tmpTable2)
VAR __Result = CONCATENATEX(__tmpTable3, [Customers], ",", [Customers])
RETURN
__Result

It returns a list of customers who bought Plan 10 and Plan 4 as below:

Customer1, Customer5, Customer 6

I want another meausre that returns a list of total customers who bought Plan 10 minus the customers who bought Plan 10 and Plan 4.

Note: The names Plan 10, Plan 4... are just for examples.

Thanks