Segmentation based on Product List available for each Customer

[ShaileshKT]

Hi Team,

I need help to build segmentation for each customer based on the list of products. Below is the example:

CustomerID	ProductID	Expected Segment
Customer1	Product1	Segment1
Customer1	Product2	Segment1
Customer1	Product3	Segment1
Customer1	Product4	Segment1
Customer1	Product5	Segment1
Customer1	Product6	Segment1
Customer1	Product7	Segment1
Customer1	Product8	Segment1
Customer2	Product1	Segment2
Customer2	Product2	Segment2
Customer2	Product3	Segment2
Customer2	Product4	Segment2
Customer2	Product7	Segment2
Customer2	Product8	Segment2
Customer3	Product2	Segment3
Customer3	Product4	Segment3
Customer3	Product7	Segment3

Please help me to achieve above result using dax.

Thank you in advance 

Best regards 

[v-moharafi-msft · Accepted solution]

HI @ShaileshKT ,

Yes, it is possible to achieve this using a calculated measure even when CustomerID and ProductID are present in different tables, provided the tables are properly related in the data model. I have successfully reproduced your requirement using the Bridge-based model .

This can be achieved by:

• Creating separate Customer and Product tables

• Using a bridge/fact table that contains the mapping between CustomerID and ProductID

• Creating relationships between these tables in the model view

• Building the segmentation logic as a measure that evaluates the related product combinations for each customer

So, you do not necessarily need to create a single combined table if the relationships are configured correctly.

If you face any difficulty while implementing it in your model, please feel free to reach out to us. We are happy to assist..

Please check out the .pbix file provided

Best Regards,

Abdul Rafi

[ShaileshKT]

Thanks a lot @v-moharafi-msft for your help. After making some small changes in the formula, it worked for me.

Best regards 

Shailesh K

[v-moharafi-msft]

Hi  @ShaileshKT ,

Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

 Best Regards,

Abdul Rafi.

[ShaileshKT]

Hi @v-moharafi-msft ,

Sorry for the late reply. I just wanted to check if  it is possible to achieve using calculated measure. As my customerID and ProductID columns are in two different tables. If it is not possible, then I have create one table which has both columns and follow your method.

Thank you in advance 

Best regards 

[v-moharafi-msft]

Hi @ShaileshKT   , 

Thank you for reaching out to Microsoft Fabric Community.

I have reproduced your scenario and was able to achieve the expected result using a calculated column in DAX.

please go through the DAX code and screenshot provided below.

The approach works by:

• Collecting all products associated with each customer
• Checking the product combination available for that customer
• Assigning the corresponding segment value for all rows of that customer

  DAX Code :


  Segment =

  VAR Products =

      CALCULATETABLE (

          VALUES ( SampleData[ProductID] ),

          ALLEXCEPT ( SampleData, SampleData[CustomerID] )

      )
  

  VAR HasP1 =

      CONTAINS ( Products, SampleData[ProductID], "Product1" )
  

  VAR HasP2 =

      CONTAINS ( Products, SampleData[ProductID], "Product2" )
  

  VAR HasP3 =

      CONTAINS ( Products, SampleData[ProductID], "Product3" )
  

  VAR HasP4 =

      CONTAINS ( Products, SampleData[ProductID], "Product4" )
  

  VAR HasP5 =

      CONTAINS ( Products, SampleData[ProductID], "Product5" )
  

  VAR HasP6 =

      CONTAINS ( Products, SampleData[ProductID], "Product6" )
  

  VAR HasP7 =

      CONTAINS ( Products, SampleData[ProductID], "Product7" )
  

  VAR HasP8 =

      CONTAINS ( Products, SampleData[ProductID], "Product8" )
  

  RETURN

  SWITCH (

      TRUE(),
  

      -- Segment1

      HasP1 && HasP2 && HasP3 && HasP4 &&

      HasP5 && HasP6 && HasP7 && HasP8,

      "Segment1",
  

      -- Segment2

      HasP1 && HasP2 && HasP3 &&

      HasP4 && HasP7 && HasP8 &&

      NOT HasP5 && NOT HasP6,

      "Segment2",
  

      -- Segment3

      HasP2 && HasP4 && HasP7 &&

      NOT HasP1 && NOT HasP3 &&

      NOT HasP5 && NOT HasP6 &&

      NOT HasP8,

      "Segment3",
  

      "Other"

  )

   

  This will assign:

  Customer1 → Segment1

  Customer2 → Segment2

  Customer3 → Segment3

  for all corresponding product rows.

  Please replace SampleData with your actual table name before using the formula.

  If you find any difficulty, please feel free to reach out to us. We are happy to assist.

  Best Regards,

  Abdul Rafi

[ShaileshKT]

Thank you @v-moharafi-msft  for your solution. I wanted to check if it is possible to achieve using calculated measure as my customerID and ProductID columns are available in two different tables.

Also, there is one change in the requirement. In the sample data, I have provided mandatoary product list. There might chances that for Customer1 has other product assigned apart from Product1 to Product8.

The segmentation defination are as follow:

Segmet1= IF any Customer has Product1 to Product8 then that assigned Segment1

Segment2=IF any Customer has Product1 to Product4, Product7 and Product8 then assigned Segment2

Segment2=IF any Customer has Product2, Product4 and Product7 then assigned Segment3

Below the sample data.

CustomerID	ProductID	Expected Segment	ProductlistSegmentation
Customer1	Product1	Segment1	Mandatory
Customer1	Product2	Segment1	Mandatory
Customer1	Product3	Segment1	Mandatory
Customer1	Product4	Segment1	Mandatory
Customer1	Product5	Segment1	Mandatory
Customer1	Product6	Segment1	Mandatory
Customer1	Product7	Segment1	Mandatory
Customer1	Product8	Segment1	Mandatory
Customer1	Product9	Segment1	Optional
Customer1	Product10	Segment1	Optional
Customer1	Product11	Segment1	Optional
Customer1	Product12	Segment1	Optional
Customer2	Product1	Segment2	Mandatory
Customer2	Product2	Segment2	Mandatory
Customer2	Product3	Segment2	Mandatory
Customer2	Product4	Segment2	Mandatory
Customer2	Product7	Segment2	Mandatory
Customer2	Product8	Segment2	Mandatory
Customer2	Product13	Segment2	Optional
Customer2	Product14	Segment2	Optional
Customer2	Product15	Segment2	Optional
Customer3	Product2	Segment3	Mandatory
Customer3	Product4	Segment3	Mandatory
Customer3	Product7	Segment3	Mandatory
Customer3	Product10	Segment3	Optional
Customer3	Product13	Segment3	Optional
Customer3	Product15	Segment3	Optional

Thank you in advance 

Best regards 

[v-moharafi-msft]

Hi  @ShaileshKT , 

I have reproduced your updated scenario with both Mandatory and Optional products, and I was able to achieve the expected result successfully.

Based on your requirement, the segmentation logic checks only the mandatory product combinations required for each segment, while any additional optional products assigned to the customer are ignored and do not impact the segment assignment.

The expected segmentation was achieved successfully for:

• Segment1 = Customers having Product1 to Product8

• Segment2  = Customers having Product1 to Product4, Product7, and Product8

• Segment3  =  Customers having Product2, Product4, and Product7

  Additional optional products do not affect the segment assignment.

  Please go through the attached PBIX file and screenshots provided below.

  Best Regards,

  Abdul Rafi