Create a column based on the text data in other columns.

[Premlatapandey9]

Hello Everyone,

Greetings for the day!

I have the below data set 

Family Name	Part No	Description	Supplier
Compute	123456	COMPUTESTORAGE-TE-GEN5.8-INTEL-104-LENOVO-48U-STANDARD	WWWT
Netapp	123457	NETAPP-STORAGE-GEN2.7-INTEL-103H-WWT	FFFT
Compute	123456	COMPUTESTORAGE-TE-GEN5.8-INTEL-104-LENOVO-48U-STANDARD	MGT
Netapp	123457	NETAPP-STORAGE-GEN2.7-INTEL-103H-WWT	FFFT
Compute	123456	NETAPP-STORAGE-GEN2.7-INTEL-103H-WWT	MGT

 and based on above i want to create a column name " Comment " like below

Family Name	Part No	Description	Supplier	Comment
Compute	123456	COMPUTESTORAGE-TE-GEN5.8-INTEL-104-LENOVO-48U-STANDARD	WWWT	Unique Combo
Netapp	123457	NETAPP-STORAGE-GEN2.7-INTEL-103H-WWT	FFFT	Dupe Combo
Compute	123456	COMPUTESTORAGE-TE-GEN5.8-INTEL-104-LENOVO-48U-STANDARD	MGT	Only Supplier is different
Netapp	123457	NETAPP-STORAGE-GEN2.7-INTEL-103H-WWT	FFFT	Dupe Combo
Compute	123456	NETAPP-STORAGE-GEN2.7-INTEL-103H-WWT	MGT	Only Description is different

Can you please help me to write the DAX for this thank you in advance!

 @member ,  @dax 

[tamerj1]

Hi @Premlatapandey9 
Based on what?

[Premlatapandey9]

Hello @tamerj1  applologies i have updated the above ask can you please have a look now, earlier the first table did not get posted Thanks!

[tamerj1]

@Premlatapandey9 
Please refer to attached sample file with the proposed solution

Comment = 
VAR IsDupe = COUNTROWS ( CALCULATETABLE ( 'Table', ALL ( 'Table'[Index] ) ) ) > 1
VAR FamilyPartTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Family Name], 'Table'[Part No] ) )
VAR TableBefore = FILTER ( FamilyPartTable, 'Table'[Index] < EARLIER ( 'Table'[Index] ) )
VAR DescriptionsBefore = DISTINCT ( SELECTCOLUMNS ( TableBefore, "@Description", 'Table'[Description] ) )
VAR SuppliersBefore = DISTINCT ( SELECTCOLUMNS ( TableBefore, "@Supplier", 'Table'[Supplier] ) )
RETURN
    SWITCH (
        TRUE ( ),
        IsDupe, "Dupe Combo",
        ISEMPTY ( TableBefore ), "Unique Combo",
        'Table'[Description] In DescriptionsBefore, "Only Supplier is different",
        'Table'[Supplier] In SuppliersBefore, "Only Description is different",
        "Both Description and Supplier are different"
    )