DAX to split and transpose

[samdthompson]

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

[OwenAuger · Accepted solution]

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.

Uploaded here to dropbox

 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

[samdthompson]

@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