Forum Discussion
DAX to de-duplicate text string?
Would it be possible to get DAX de-duplicating a text string? In the example below - or any suggestions on alternate ways, but still using DAX 🙂 thanks in advance
| Address | Area | Distribution | string = CONCATENATEX(FILTER(('Table'),'Table'[Address]=EARLIER('Table'[Address])),'Table'[Area],"; ") | would then like it de-duplicated -- but wondered if DAX could facilitate? |
| George Street | 1st Floor | Zone 3 | 1st Floor; 1st Floor; 2nd Floor | 1st Floor; 2nd Floor |
| George Street | 2nd Floor | Zone 1 | 1st Floor; 1st Floor; 2nd Floor | 1st Floor; 2nd Floor |
| George Street | 1st Floor | Zone 5 | 1st Floor; 1st Floor; 2nd Floor | 1st Floor; 2nd Floor |
| Lime Walk | Basement | Zone 7 | Basement; Basement | Basement |
| Lime Walk | Basement | Zone 4 | Basement; Basement | Basement |
| Hope Avenue | Basement | Zone 2 | 1st Floor; 1st Floor; Basement | Basement; 1st Floor |
| Hope Avenue | 1st Floor | Zone 8 | 1st Floor; 1st Floor; Basement | Basement; 1st Floor |
| Hope Avenue | 1st Floor | Zone 5 | 1st Floor; 1st Floor; Basement | Basement; 1st Floor |
| Market Square | 2nd Floor | Zone 6 | 2nd Floor | 2nd Floor |
Try this.DeDuplicatedString =
CALCULATE(
CONCATENATEX(
VALUES('Table'[Area]), -- <-- this removes duplicates
'Table'[Area],
"; ",
'Table'[Area]
),
ALLEXCEPT('Table', 'Table'[Addr
ess])
)
4 Replies
Try this.DeDuplicatedString =
CALCULATE(
CONCATENATEX(
VALUES('Table'[Area]), -- <-- this removes duplicates
'Table'[Area],
"; ",
'Table'[Area]
),
ALLEXCEPT('Table', 'Table'[Addr
ess])
)
I do not get the question
Chanty4u is right. You can also only use
String =CONCATENATEX(
VALUES('Table'[Area]), -- <-- this removes duplicates
'Table'[Area],
"; ",
'Table'[Area]
If u only us the adresHi JK-1 please try this calculated column
DeDuplicatedAreas =VAR CurrentAddress = AddressAreas[Address]VAR DistinctAreas =CALCULATETABLE(VALUES(AddressAreas[Area]),FILTER(ALL(AddressAreas),AddressAreas[Address] = CurrentAddress))RETURNCONCATENATEX(DistinctAreas,AddressAreas[Area],"; ",AddressAreas[Area], ASC)
