Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Concat based on the column text

 

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.

 

ITEMSALES CODEDESIRED RESULT
1234590019001,9002,9003,9004
1234590029001,9002,9003,9004
1234590039001,9002,9003,9004
1234590049001,9002,9003,9004
123459005 
123459006 
123459007 
123459008 
123459009 
123459010 
12345  
546849005 
546849006 
546849007 
546849008 
546849009 
546849010 
546849011 
54684  
89790019001,9002,9003,9004
89790029001,9002,9003,9004
89790039001,9002,9003,9004
89790049001,9002,9003,9004
897  
56423190049004,9003,9002,9001
5642319010 
5642319011 
56423190039004,9003,9002,9001
5642319005 
5642319006 
56423190029004,9003,9002,9001
56423190019004,9003,9002,9001
34590019001
34590109001

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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.

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Hi,

       

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

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        5 years ago

        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.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        Saxon10
        Icon for Post Prodigy rankPost Prodigy
        5 years ago

        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.

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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)