I made a few changes to your load script.
1. In table "References Compatibility Products", I renamed "Main Reference" to "Reference Number".
2. In table "References Compatibility Products", I added a column called "Compatibility Seq". In your script, use the line
RowNo() as Compatibility Seq. This ensures that the resulting table is sorted the way yours is in the example.
3. I also added another resident load for table "References Compatibility Products" to add a record for each of the main Reference Numbers.
Adapt this script to your database loads and run it. Then create the table. If you filter on ABC, you'll get the correct result (see screenshot).
[Facts Table]:
Load * Inline [
Doc Data, Doc Number, Reference, Qty
2/1/2018, 1801, ABC, 2
3/24/2018, 1802, ABD, 10
8/6/2018, 1803, ABC, 1
12/1/2018, 1804, ABG, 4
2/1/2018, 1801, CBC, 20
3/24/2018, 1802, CBD, 50
8/6/2018, 1803, CBC, 20
12/1/2018, 1804, CBG, 20
];
[Reference Table]:
Load * Inline [
Reference No, Reference Description
ABC, PRINTER A
ABD, PRINTER A TONER
ABG, PRINTER A DRUM
CBC, PRINTER C
CBD, PRINTER C TONER
CBG, PRINTER C DRUM
];
[References Compatible Products]:
Load * Inline [
Reference No, Compatible Reference, Compatible Reference Description, Compatible Seq
ABC, ABD, PRINTER A TONER, 1
ABC, ABG, PRINTER A DRUM, 2
CBC, CBD, PRINTER C TONER, 3
CBC, CBG, PRINTER C DRUM, 4
];
[References Compatible Products]:
Concatenate Load Distinct
[Reference No],
[Reference No] as [Compatible Reference],
99 as [Compatible Seq]
Resident [References Compatible Products];
Data Section
Dim: [Reference No]
Dim: [Reference Description]
Dim [Compatible Reference] {Uncheck "Include Null Values"}
Measure: MaxString([Compatible Reference Description])
Measure: Sum(If(Reference=[Compatible Reference], Qty))
Sort Section
[Compatible Reference]: Change the sort on this to an expression. The expression is [Compatible Seq].
