Disctinct values in a Conactenatex with multiple values.

Question

Hi All, I'm looking for a solution to a problem I'm having.&nbsp;I have two tabels:&nbsp;(Vendor) Stock Orders 2FactKey&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Supplier&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SupplierCountryFAC-001Supplier1NLFAC-001Supplier2NLFAC-002Supplier1NLFAC-002Supplier3DEFAC-003Supplier1NL&nbsp;Posted Sales Invoice Subform (Q-MC)(Supplier and SupplierCountry are calculated columns)PostedSalesKey&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Item&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Amount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Supplier&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SupplierCountryFAC-001Item1100Supplier1, Supplier2NL, NLFAC-001Item2200Supplier1, Supplier2NL, NLFAC-002Item1100Supplier1, Supplier3NL, DEFAC-002Item3300Supplier1, Supplier3NL, DEFAC-003Item1100Supplier1NLSupplier is right, but I would like SupplierCountry to show only disctinct values. So for FAC-001 I would like to show NL only once.&nbsp;This is the code I'm using for the calculated colum:&nbsp;&nbsp; SupplierCountry = CONCATENATEX (
        FILTER (
            ALL ( '(Vendors) Stock Orders 2' ),
            '(Vendors) Stock Orders 2'[FactKey] = 'Posted Sales Invoice Subform (Q-MC)'[PostedSalesKey]
        ),
        '(Vendors) Stock Orders 2'[SupplierCountry],
        ", "
    )&nbsp;&nbsp;Does anyone know how to solve this? I have tried adding Disctinct in diffrent places, but without succes.Thanks in advance!

greg_deckler · Accepted Answer

Emiel99&nbsp;I *think*
 SupplierCountry = CONCATENATEX (
    DISTINCT(
      SELECTCOLUMNS(
        FILTER (
            ALL ( '(Vendors) Stock Orders 2' ),
            '(Vendors) Stock Orders 2'[FactKey] = 'Posted Sales Invoice Subform (Q-MC)'[PostedSalesKey]
        ),
        "__SupplierCountry", [SupplierCountry]
      )
    ),
    [__SupplierCountry],
        ", "
)

Forum Discussion

Disctinct values in a Conactenatex with multiple values.

2 Replies

Related content

MULTIPLE CARD

Multiple dataViewMappings

Merge data from multiple excel files from multiple sharepoint folders

Disable multiple eventstreams

IF, Multiple

FactKey	Supplier	SupplierCountry
FAC-001	Supplier1	NL
FAC-001	Supplier2	NL
FAC-002	Supplier1	NL
FAC-002	Supplier3	DE
FAC-003	Supplier1	NL

PostedSalesKey	Item	Amount	Supplier	SupplierCountry
FAC-001	Item1	100	Supplier1, Supplier2	NL, NL
FAC-001	Item2	200	Supplier1, Supplier2	NL, NL
FAC-002	Item1	100	Supplier1, Supplier3	NL, DE
FAC-002	Item3	300	Supplier1, Supplier3	NL, DE
FAC-003	Item1	100	Supplier1	NL