PowerBI - Best way to split column as new rows, enrich and include in report

[zachlovescoffee]

Hello there,

I'm trying to figure out the best way to enrich a table of orders with the country region (Americas, Asia, Europe, Africa, etc) when the country field has multiple concatenations of country in a single record. I know that I can split the column as new rows, but that would expand the length of the table dramatically, which could affect performance. The other thing that I need to care for, is that at the pre-split record level, there is a customer label like "High", "Low", "Mod" and in visualizations I need to be able to show a distinct aggregation of how many High, Low and Moderate customers I have so the duplicated rows are throwing it off.

Target table example

ID | Country | FieldA | FieldB | fields C-Z |

1.	United States;Taiwan;Hong Kong;Bermuda	abc	123	etc
2.	Canada	qqq.	fvvv.	etc
3.	Spain; Portugal; Australia	xxz	1245	etc

What I've done so far within PowerQuery is taken a 'reference' of the target table and removed all columns except the 'order #' and 'country'. I then made some replacements in the reference table to fix some country names that aren't formatted to the correct ISO standard. E.g., 'Hong Kong' to 'Hong Kong S.A.R.'. I also merged that reference table with a dimension table of country_region.

So now the reference table looks like:

ID Country FieldA FieldB Region

1.	United States	123	abc	Americas
1.	Taiwan	abc	123	Asia
1.	Hong Kong S.A.R.	abc	123	Asia
1.	Bermuda	abc	123	Other
2.	Canada	qqq	fvvv.	Americas
3.	Spain	xxz	1245	Europe
3.	Portugal	xxz	1245	Europe

This works quite well in a limited sense. I can plot the geo locations/order values from this reference table on a report along side other tables/fields. I also created measures for each Region so that I can plot them on a data card to show exposure:

countAfrica = Calculate ( 
  Count ( 'ref_tbl~ctry'[Order#] ), 
    Filter ( 
       All ( 'ref_tbl~ctry' ),
        ( 'ref_tbl~ctry'[dim-map.region ) = 'Africa'

However, when a user clicks one of the bubbles on the map, it doesn't filter other visuals or tables on the page where the source data is the original target table.

What's the best strategy for enriching a table of concatenated country strings with the region and plotting those on a map? Should I just split columns as new rows and enrich in the target table and then treat individual visualizations and tables individually? I.e., certain visuals would use count data irrespective of distinct order #s and certain fields would use distinct?

Open to all kinds of feedback here as I'm a new PBI developer. I'm reading Definitive DAX Guide, Ultimate PowqerQuery Cookbook, PowerBI Exam Prep book, endlessly consuming and excited to learn more!

[zachlovescoffee]

Hi Adam,

I ended up just enriching my target table as the performance tradeoff wasn't that big. That being said, let me try to answer your questions with some steps:

1) Create original table and do all of the necessary transformations

1a) Create a dim-map table and load it with ISO standard country names, regions and subregions

2) Create a reference table from the original table and perform transformations

2a) Keep only order #, customer rating, country name as the 'big steps'

3) Join ref_tbl to dim-map and merge on country name (1:1) and expand ref_tbl with the region and sub-region fields

4) Prepare visuals where most of the visuals on the page pull their data from Original table, including a matrix vis

5) Add map vis but have its elements populated from the ref_tbl data (order #, country)

6) Click any bubble on the map and the map itself will 'filter' on the screen but affects no other visual or matrix on the screen

My guess is that the ref_tbl isn't 'related' to any other visual on the screen so that's why selection, "europe' for example on the map doesn't filter a table vis on the screen whose data comes from original table. Even though there is a 'europe' in. both, they have nothing to relate them.

I assumed that because the reference table (ref_tbl) was literally a reference of the original table, that any filters applied to either, on the same page, would affect the visuals of one another.

[Anonymous]

Hi @zachlovescoffee ,

I can't reproduce this situation: when the user clicks on one of the bubbles on the map, it doesn't filter the other visual objects or tables on the page where the source data is the original target table.

Can you provide some screenshot information and describe it?

Best Regards,
Adamk Kong