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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.