Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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
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.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |