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

Concat with duplication based on the text column (DAX REQ)

 

Hi,

 

In data table the same item has multiple text so I am trying to get the text without duplication within the same cell based on the text column according to the item. If same item has more than one text then return the text with comma within the same cell.

 

I am attempting the below mentioned DAX but it's giving duplication result but desired reult is unique text with comma.

 

DAX FOR TEXT = CALCULATE(CONCATENATEX(DATA,DATA[COUNTRY],","),FILTER(DATA,DATA[ITEM]=EARLIER(DATA[ITEM])))
 

 

Data:

 

ITEMCOUNTRYDRSIRED RESULT
123ADMKADMK,DMK,BJB
123ADMKADMK,DMK,BJB
123ADMKADMK,DMK,BJB
123ADMKADMK,DMK,BJB
123DMKADMK,DMK,BJB
123DMKADMK,DMK,BJB
123BJBADMK,DMK,BJB
123BJBADMK,DMK,BJB
123BJBADMK,DMK,BJB
123BJBADMK,DMK,BJB
123BJBADMK,DMK,BJB
123BJBADMK,DMK,BJB
1235ADMKADMK,DMK
1235DMKADMK,DMK
1235DMKADMK,DMK
6789ADMKADMK
4342DMKDMK
4342DMKDMK
4342DMKDMK
4342DMKDMK

 

PBI snap shot 

 

Saxon10_0-1618040272572.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @Saxon10 

Please correct me if I wrongly understood your logic.

I am not sure if the desired outcome is showing the correct logic for the last four rows.

Please check the below Calcualted Column.

 

New Column in Table2 =
VAR currentitem = Table2[ITEM]
VAR currentitemtablewithout700800 =
SUMMARIZE (
FILTER (
Table2,
Table2[ITEM] = currentitem
&& Table2[AREA CODE] <> 700
&& Table2[AREA CODE] <> 800
),
Table2[COUNTRY]
)
RETURN
CONCATENATEX ( currentitemtablewithout700800, Table2[COUNTRY], ", " )

 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

View solution in original post

6 REPLIES 6
Jihwan_Kim
Community Champion
Community Champion

Hi, @Saxon10 

 

Please try the below for the Calculated Column.

 

New Column =
VAR currentitem = Data[ITEM]
VAR currentitemtable =
SUMMARIZE ( FILTER ( data, Data[ITEM] = currentitem ), Data[COUNTRY] )
RETURN
CONCATENATEX ( currentitemtable, Data[COUNTRY], ", " )

 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

This is working well. Thanks for your quick reply.

Thanks for your reply. I will check and come back to you. Can you please advise my another scanario when you have a time.

 

Saxon10
Post Prodigy
Post Prodigy

I have one more scenario here can you please advise.


The same logic and DAX but I would like to apply filter by area code 300 & 400 only and exclude the both area code 700 and 800 in same exciting DAX.


DAX FOR TEXT = CALCULATE(CONCATENATEX(DATA1,DATA1[COUNTRY],","),FILTER(DATA1,DATA1[ITEM]=EARLIER(DATA1[ITEM])))



ITEM COUNTRY AREA CODE DESIRED RESULT (EXCLUDE BY 800 and 700)
123 ADMK1 300 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 ADMK2 300 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 ADMK3 300 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 ADMK4 300 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 DMK1 400 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 DMK2 400 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 BJB1 800 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 BJB2 800 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 BJB3 800 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 BJB4 800 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 BJB5 800 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 BJB6 800 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 PAMAKA1 700 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 PAMAKA2 700 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 PAMAKA3 700 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
123 PAMAKA4 700 ADMK1,ADMK2,ADMK3,ADMK4,DMK1,DMK2
1235 ADMK1 300 ADMK1,DMK1,DMK2
1235 DMK1 400 ADMK1,DMK1,DMK2
1235 DMK2 400 ADMK1,DMK1,DMK2
6789 ADMK1 300 ADMK1
4342 DMK1 300 DMK1
4342 DMK2 300 DMK2
4342 DMK3 300 DMK3
4342 DMK4 300 DMK4




Saxon10_0-1618043855868.png

 

Jihwan_Kim
Community Champion
Community Champion

Hi, @Saxon10 

Please correct me if I wrongly understood your logic.

I am not sure if the desired outcome is showing the correct logic for the last four rows.

Please check the below Calcualted Column.

 

New Column in Table2 =
VAR currentitem = Table2[ITEM]
VAR currentitemtablewithout700800 =
SUMMARIZE (
FILTER (
Table2,
Table2[ITEM] = currentitem
&& Table2[AREA CODE] <> 700
&& Table2[AREA CODE] <> 800
),
Table2[COUNTRY]
)
RETURN
CONCATENATEX ( currentitemtablewithout700800, Table2[COUNTRY], ", " )

 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

View solution in original post

Thank for your reply. Yes, you are right the last 4 columns showing wrong result becasue I make manually that's the reason but your solution working well. 

 

Can you please advise and help me regarding "VAR" logic , it's hard to understand the "VAR" logic in power BI. Where and how can I use the VAR logic. How it will work in Power BI background because most of my query is complex so maybe your advise will help for me.

 

Thank you.

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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors