Forum Discussion
Memorable MemberDAX to split and transpose
Hello,
I have a model with a significant amount of text in the fact table and a second table of key words. I have created a column using CONCATENATEX to return all instances of matching terms with a delimiter, for example I get:
Fact row 1: London, UK | New York, USA | Moscow, Russia | Cairo, Egypt
Fact row 2: Nadi, Fiji
Fact row 3: Cairo, Egypt | Montreal, Canada
Fact row 4: Sydney, Australia | Moscow, Russia
I would like to be able to show the locations on a map if a user selects the data in one of the fact rows, however the data is all sitting in the one column in its concatenated form. I was thinking the calculation needs to:
1. split by delimiter based on SELECTEDVALUES
2. transpose the values
3. hold data in a CALCULATEDTABLE
Since the matches are done by DAX the solution needs to be DAX rather than PQ. I am at a loss as to how to achieve this or if it is indeed achievable.
Help appreciated,
Regards
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
2 Replies
- OwenAuger
Super User
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
samdthompsonOwenAuger, well done that man, well done indeed. Had not thought of generate at all. Thanks very much for this. its bang on.
Cheers,
Sam