Forum Discussion

ShaileshKT's avatar
ShaileshKT
Frequent Visitor
5 months ago
Solved

Segmentation based on Product List available for each Customer

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 Cust...
  • v-moharafi-msft's avatar
    v-moharafi-msft
    5 months ago

    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's avatar
ShaileshKT
Frequent Visitor
5 months ago

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.

CustomerIDProductIDExpected SegmentProductlistSegmentation
Customer1Product1Segment1Mandatory
Customer1Product2Segment1Mandatory
Customer1Product3Segment1Mandatory
Customer1Product4Segment1Mandatory
Customer1Product5Segment1Mandatory
Customer1Product6Segment1Mandatory
Customer1Product7Segment1Mandatory
Customer1Product8Segment1Mandatory
Customer1Product9Segment1Optional
Customer1Product10Segment1Optional
Customer1Product11Segment1Optional
Customer1Product12Segment1Optional
Customer2Product1Segment2Mandatory
Customer2Product2Segment2Mandatory
Customer2Product3Segment2Mandatory
Customer2Product4Segment2Mandatory
Customer2Product7Segment2Mandatory
Customer2Product8Segment2Mandatory
Customer2Product13Segment2Optional
Customer2Product14Segment2Optional
Customer2Product15Segment2Optional
Customer3Product2Segment3Mandatory
Customer3Product4Segment3Mandatory
Customer3Product7Segment3Mandatory
Customer3Product10Segment3Optional
Customer3Product13Segment3Optional
Customer3Product15Segment3Optional

 

Thank you in advance 

Best regards 

v-moharafi-msft's avatar
v-moharafi-msft
Icon for Community Support rankCommunity Support
5 months ago

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