Hi All,Here is a table with two product groups, actualy substituting each other. Non of the products has superiority. Where the colored groups represent the proximity. Here is the table view:Prod_1Prod_2Group Final00214087561002141426710027309783200278118913002840982940875600214109783002732098290028441189100278314267002141 Please note that, the products in one group do not appear in another groups. The groups are somehow unique. I would like to automaticaly index those groups, like given in the column "Group filter", independent from recordset lengst.Waitinf ror your proffesional support. Thanks in advance,

I think I found a problem: Here are the combinations, which are not grouped vise-versa in the original recordset:Meaning that:08756 - 00214 - Couple should be also appeared in the recordset as 00214-08756 record and so on..Am I right?I will ask my ERP administrator to correct the records and I will check your tamerj1 formula again.Thanks a lot once again.

Forum Discussion

George1973

Helper V

4 years ago

Solved

Grouping subsidiary products

Hi All,

Here is a table with two product groups, actualy substituting each other. Non of the products has superiority.

Where the colored groups represent the proximity. Here is the table view:

Prod_1	Prod_2	Group Final
00214	08756	1
00214	14267	1
00273	09783	2
00278	11891	3
00284	09829	4
08756	00214	1
09783	00273	2
09829	00284	4
11891	00278	3
14267	00214	1

Please note that, the products in one group do not appear in another groups. The groups are somehow unique.

I would like to automaticaly index those groups, like given in the column "Group filter", independent from recordset lengst.

Waitinf ror your proffesional support.

Thanks in advance,

tamerj1

4 years ago

Hi George1973
Please try the following

Group Final = 
VAR Prod_1Rank =
    RANKX ( 
        Products,
        Products[Prod_1],,
        ASC,
        Dense
    )
VAR Prod_2Rank =
    RANKX ( 
        Products,
        Products[Prod_2],,
        ASC,
        Dense
    )
RETURN
    MIN ( Prod_1Rank, Prod_2Rank )

George1973
4 years ago
I think I found a problem:

Here are the combinations, which are not grouped vise-versa in the original recordset:
Meaning that:
08756 - 00214 - Couple should be also appeared in the recordset as 00214-08756 record and so on..
Am I right?
I will ask my ERP administrator to correct the records and I will check your tamerj1 formula again.

Thanks a lot once again.

6 Replies

tamerj1
Community Champion
4 years ago
Hi George1973
Please try the following
Group Final = VAR Prod_1Rank = RANKX ( Products, Products[Prod_1],, ASC, Dense ) VAR Prod_2Rank = RANKX ( Products, Products[Prod_2],, ASC, Dense ) RETURN MIN ( Prod_1Rank, Prod_2Rank )
- George1973
  Helper V
  4 years ago
  Hi tamerj1 ,
  The solution you've provided seems very interesting and easy. But somehow in some combinations id does not work, unfortunately. For instance:
  
  This group should have the same number, but the measure generates two different indexes.. here is the cross check (the second picture)
  As you can see right and left columns are identical in terms of content of analogs, but somehow the group index is different.
  
  Here is another example:
  
  In some cases the calculation of the index is right, like here:
  
  I do not understand why it happens?!
  
  Here is the original data:
  IDPRODANALOGPRODAnalog_Group
  - George1973
    Helper V
    4 years ago
    I think I found a problem:
    
    Here are the combinations, which are not grouped vise-versa in the original recordset:
    Meaning that:
    08756 - 00214 - Couple should be also appeared in the recordset as 00214-08756 record and so on..
    Am I right?
    I will ask my ERP administrator to correct the records and I will check your tamerj1 formula again.
    
    Thanks a lot once again.