Concat based on the column text

[Saxon10]

Hi,

In data table I have following columns are item and sales code. The sales code contain number and item code contain number and text.

I am trying ignore the following sales codes (<> from 9005 to 9010 and blanks) while CONCATENATEX the sales code based on the item.

I am using the following DAX 

CONCOR = VAR SALESCODE = CALCULATETABLE(VALUES('DATA (2)'[SALES CODE]),FILTER(ALL('DATA (2)'),'DATA (2)'[ITEM]=EARLIER('DATA (2)'[ITEM]))) RETURN CONCATENATEX(SALESCODE,'DATA (2)'[SALES CODE],",") but I don't know how can I apply the <> function (<> from 9005 to 9010 and blanks) in the same DAX.

Any idea please.

ITEM	SALES CODE	DESIRED RESULT
12345	9001	9001,9002,9003,9004
12345	9002	9001,9002,9003,9004
12345	9003	9001,9002,9003,9004
12345	9004	9001,9002,9003,9004
12345	9005
12345	9006
12345	9007
12345	9008
12345	9009
12345	9010
12345
54684	9005
54684	9006
54684	9007
54684	9008
54684	9009
54684	9010
54684	9011
54684
897	9001	9001,9002,9003,9004
897	9002	9001,9002,9003,9004
897	9003	9001,9002,9003,9004
897	9004	9001,9002,9003,9004
897
564231	9004	9004,9003,9002,9001
564231	9010
564231	9011
564231	9003	9004,9003,9002,9001
564231	9005
564231	9006
564231	9002	9004,9003,9002,9001
564231	9001	9004,9003,9002,9001
345	9001	9001
345	9010	9001

[AlB]

Hi @Saxon10 

Watch out. The description is inconsistent with the desired result (9011). Try this:

New Col =
VAR exclude_ =
    UNION ( GENERATESERIES ( 9005, 9010 ), ROW ( "Value", BLANK () ) )
VAR auxT_ =
    FILTER (
        CALCULATETABLE (
            DISTINCT ( Table1[SALES CODE] ),
            ALLEXCEPT ( Table1, Table1[ITEM] )
        ),
        NOT Table1[SALES CODE] IN exclude_
    )
RETURN
    IF (
        NOT Table1[SALES CODE] IN exclude_,
        CONCATENATEX ( auxT_, Table1[SALES CODE], ",", Table1[SALES CODE], ASC )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Saxon10]

Hi,

Thanks for your reply as well.

Sorry I prepared the desired result manually that's the reason it went wrong. Thanks for your solution as well. Nice to learn new function (Union)

[AlexisOlson]

Yes, @Saxon10 watch out for the discrepancies. In addition to the row with 9011, the bottom row in your post also does not match your description.

[Saxon10]

Hi,

Thanks for your reply again.

Sorry I prepared the desired result manually that's the reason it went wrong. Thanks for address the discrepancies.

[AlexisOlson · Accepted solution]

The key here is the EXCEPT function to eliminate the stuff you don't want.

CONCOR = 
VAR Blacklist = { 9005, 9006, 9007, 9008, 9009, 9010, BLANK () }
VAR Fulllist =
    CALCULATETABLE (
        VALUES ( 'DATA (2)'[SALES CODE] ),
        ALLEXCEPT ( 'DATA (2)', 'DATA (2)'[ITEM] )
    )
RETURN
    IF (
        'DATA (2)'[SALES CODE] IN Blacklist,
        BLANK (),
        CONCATENATEX ( EXCEPT ( Fulllist, Blacklist ),  'DATA (2)'[SALES CODE], "," )
    )

You can also write the Blacklist as UNION ( GENERATESERIES ( 9005, 9010 ), { BLANK () } ), which can easily extend to much larger ranges of values.

[Saxon10]

Hi,

Thanks for your quick reply and trick of the EXPECT function.

[AlexisOlson]

You could write it without the EXCEPT function like FILTER ( Fulllist, NOT 'Data (2)'[SALES CODE] IN Blacklist ) but this is the perfect use-case for that particular function.

[Saxon10]

Thank you so much for your addtional information and advise. I will try to do my self.