Forum Discussion

alir22456's avatar
alir22456
Icon for Helper I rankHelper I
2 years ago

Power Query DAX

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

Need Help

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      alir22456
      Icon for Helper I rankHelper I
      2 years ago

      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