cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samdthompson Established Member
Established Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: 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.

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



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

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
OwenAuger Super Contributor
Super Contributor

Re: 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.

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



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

Proud to be a Datanaut!




View solution in original post

samdthompson Established Member
Established Member

Re: DAX to split and transpose

@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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 68 members 983 guests
Please welcome our newest community members: