cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saxon10
Post Prodigy
Post Prodigy

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

 

Saxon10_0-1619622683296.png

 

1 ACCEPTED SOLUTION
AlexisOlson
Resident Rockstar
Resident Rockstar

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 ( 90059010 ), { BLANK () } ), which can easily extend to much larger ranges of values.

View solution in original post

8 REPLIES 8
AlB
Super User III
Super User III

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 )
    )

 

SU18_powerbi_badge

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.

 

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
Resident Rockstar
Resident Rockstar

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.

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
Resident Rockstar
Resident Rockstar

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 ( 90059010 ), { BLANK () } ), which can easily extend to much larger ranges of values.

View solution in original post

Hi,

 

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

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.

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors