Forum Discussion
DAX to split and transpose
Hi samdthompson
If you need to do this in DAX, I would suggest that rather than concatenating in the first place, you use GENERATE to create a two-column table containing the Text values (repeated) and all matching Keywords.
Here's an example. I'm guessing a bit with your exact table structure but hopefully this can be adapted.
Text table
Text ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ ZZZ Nadi ZZZ ZZZ Cairo ZZZ Montreal ZZZ ZZZ Sydney ZZZ Moscow ZZZ Keyword table
Keyword Lookup Keyword Full London London, UK New York New York, USA Moscow Moscow, Russia Cairo Cairo, Egypt Nadi Nadi, Fiji Montreal Montreal, Canada Sydney Sydney, Australia DAX to create calculated table Text and Keyword table
Text and Keyword = GENERATE ( 'Text', VAR MatchingKeywords = FILTER ( Keyword, NOT ISERROR ( SEARCH ( Keyword[Keyword Lookup], 'Text'[Text] ) ) ) RETURN SELECTCOLUMNS ( MatchingKeywords, "Keyword", Keyword[Keyword Full] ) )Resulting Text and Keyword table
Text Keyword ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ London, UK ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ New York, USA ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ Moscow, Russia ZZZ Sydney ZZZ Moscow ZZZ Moscow, Russia ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ Cairo, Egypt ZZZ Cairo ZZZ Montreal ZZZ Cairo, Egypt ZZZ Nadi ZZZ Nadi, Fiji ZZZ Cairo ZZZ Montreal ZZZ Montreal, Canada ZZZ Sydney ZZZ Moscow ZZZ Sydney, Australia You can then slice on Text and put the Keyword on a map visual:
Regards,
Owen
Hi samdthompson
If you need to do this in DAX, I would suggest that rather than concatenating in the first place, you use GENERATE to create a two-column table containing the Text values (repeated) and all matching Keywords.
Here's an example. I'm guessing a bit with your exact table structure but hopefully this can be adapted.
Text table
| Text |
| ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ |
| ZZZ Nadi ZZZ |
| ZZZ Cairo ZZZ Montreal ZZZ |
| ZZZ Sydney ZZZ Moscow ZZZ |
Keyword table
| Keyword Lookup | Keyword Full |
| London | London, UK |
| New York | New York, USA |
| Moscow | Moscow, Russia |
| Cairo | Cairo, Egypt |
| Nadi | Nadi, Fiji |
| Montreal | Montreal, Canada |
| Sydney | Sydney, Australia |
DAX to create calculated table Text and Keyword table
Text and Keyword =
GENERATE (
'Text',
VAR MatchingKeywords =
FILTER (
Keyword,
NOT ISERROR ( SEARCH ( Keyword[Keyword Lookup], 'Text'[Text] ) )
)
RETURN
SELECTCOLUMNS ( MatchingKeywords, "Keyword", Keyword[Keyword Full] )
)
Resulting Text and Keyword table
| Text | Keyword |
| ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ | London, UK |
| ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ | New York, USA |
| ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ | Moscow, Russia |
| ZZZ Sydney ZZZ Moscow ZZZ | Moscow, Russia |
| ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZ | Cairo, Egypt |
| ZZZ Cairo ZZZ Montreal ZZZ | Cairo, Egypt |
| ZZZ Nadi ZZZ | Nadi, Fiji |
| ZZZ Cairo ZZZ Montreal ZZZ | Montreal, Canada |
| ZZZ Sydney ZZZ Moscow ZZZ | Sydney, Australia |
You can then slice on Text and put the Keyword on a map visual:
Regards,
Owen
OwenAuger, well done that man, well done indeed. Had not thought of generate at all. Thanks very much for this. its bang on.
Cheers,
Sam