Grouping subsidiary products

[George1973]

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 · Accepted solution]

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 )

[tamerj1 · Accepted solution]

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]

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

[tamerj1]

hi @George1973 
Actually I've expected that but I did'nt have enough data to find a proper solution. 
I have some ideas on how to proceed. I'll go through the file once I'm on my PC and let you know if was able to find a solution. 

[George1973 · Accepted solution]

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.

[tamerj1]

@George1973 

Ok great 👍 

[George1973]

Hi @tamerj1 ,
We have not updated data in the origanal ERP system, but the solution you've provided seems to be correct. Thanks a lot once again.