Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
samdthompson
Memorable Member
Memorable 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

// if this is a solution please mark as such. Kudos always appreciated.
1 ACCEPTED SOLUTION
OwenAuger
Super User
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.

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!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
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.

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!
Blog
Twitter
LinkedIn

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.