cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Impactful Individual
Impactful Individual

DAX 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

// if this is a solution please mark as such. Kudos always appreciated.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

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 LookupKeyword Full
LondonLondon, UK
New YorkNew York, USA
MoscowMoscow, Russia
CairoCairo, Egypt
NadiNadi, Fiji
MontrealMontreal, Canada
SydneySydney, 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

TextKeyword
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZLondon, UK
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZNew York, USA
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZMoscow, Russia
ZZZ Sydney ZZZ Moscow ZZZMoscow, Russia
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZCairo, Egypt
ZZZ Cairo ZZZ Montreal ZZZCairo, Egypt
ZZZ Nadi ZZZNadi, Fiji
ZZZ Cairo ZZZ Montreal ZZZMontreal, Canada
ZZZ Sydney ZZZ Moscow ZZZSydney, Australia

 

You can then slice on Text and put the Keyword on a map visual:image.png

 

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
Highlighted
Super User I
Super User I

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 LookupKeyword Full
LondonLondon, UK
New YorkNew York, USA
MoscowMoscow, Russia
CairoCairo, Egypt
NadiNadi, Fiji
MontrealMontreal, Canada
SydneySydney, 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

TextKeyword
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZLondon, UK
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZNew York, USA
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZMoscow, Russia
ZZZ Sydney ZZZ Moscow ZZZMoscow, Russia
ZZZ London aaa New York ZZZ Moscow ZZZ Cairo ZZZCairo, Egypt
ZZZ Cairo ZZZ Montreal ZZZCairo, Egypt
ZZZ Nadi ZZZNadi, Fiji
ZZZ Cairo ZZZ Montreal ZZZMontreal, Canada
ZZZ Sydney ZZZ Moscow ZZZSydney, Australia

 

You can then slice on Text and put the Keyword on a map visual:image.png

 

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted

@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

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors