topic Re: Segmentation based on Product List available for each Customer in DAX Commands and Tips

Segmentation based on Product List available for each Customer

ShaileshKT — Thu, 14 May 2026 06:09:11 GMT

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

Re: Segmentation based on Product List available for each Customer

v-moharafi-msft — Thu, 14 May 2026 10:40:14 GMT

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

Re: Segmentation based on Product List available for each Customer

ShaileshKT — Thu, 14 May 2026 11:55:36 GMT

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

Re: Segmentation based on Product List available for each Customer

v-moharafi-msft — Fri, 15 May 2026 04:45:45 GMT

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

Re: Segmentation based on Product List available for each Customer

v-moharafi-msft — Mon, 18 May 2026 05:02:06 GMT

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.

Re: Segmentation based on Product List available for each Customer

ShaileshKT — Mon, 18 May 2026 07:14:09 GMT

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

Re: Segmentation based on Product List available for each Customer

v-moharafi-msft — Mon, 18 May 2026 10:34:33 GMT

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

Re: Segmentation based on Product List available for each Customer

ShaileshKT — Tue, 19 May 2026 13:23:03 GMT

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