Current and potential customers on same map

[Anonymous]

Hi,

I'm trying to get Current customers-data coming from our ERP system, and potential customers-data coming from an excel file sales people update.

I'm struggling with being able to filter both tables using the Location Key table I created and putting it on a map to show both current and potential customers on the map at the same time.

My location key filter will display current customers on the map but when I choose potential they don't show up. I feel like I'm missing something simple here. I don't have lat or long data, only city and state. I have attached screen shots.

Any help would be appreciate.

[edhans · Accepted solution]

@Anonymous ,

Not knowing much about your data, the approach I would take is this:

1. Potential Customer data and Actual Customer data are the same thing - Customer data, so it should be in one table.
2. Add a new column at the source or in Power Query called "Customer Type" (or whatever) with the formula in a custom column that is simply ="Actual" - make it a text column.
3. Add a new column in the Excel file via Power Query (do it there vs Excel so you don't have to remember to ever do it in Excel again as new rows come in" with the same column name that is the following formula ="Potential" - make it a text column.
4. Harmonize any other columns names as necessary between the actual data and the excel file.
5. In the actual data, select Append Queries on the home ribbon, and select the Excel file.
6. Now, make sure to right-click on the Excel file query itself and make sure "Enable Load" is not checked.

Any columns that exist in the actual data that do not exist in the potential data will be null in the potential records. The reverse is also true - Excel columns will generate null fields in the actual data. 

But now you can simply add data and filter on the Actual/Potential, or create slicers, legends, etc.

[Anonymous]

@edhans 

This was the by far the correct answer. The mistake I made was that I didn't harmonize my year column name because I simply forgot. My year slicer had a blank and guess what? That blank represented my potential customers.

I'll go back and do the steps again and correctly name everything. Thank you for the help. This community is really great.

[edhans]

Awesome @Anonymous - glad I was able to help out. 

[Anonymous]

@edhans 

So this is weird... I went and looked and discovered this...

Any reason for the slicer not to work even when both options are present in the column? 

[Anonymous]

@edhans 

This is close to what I already did but I didn't do the step of Un-checking enable load. However, this doesn't work. I use the current/potential column as a slicer and It's only finding current. No option now for potential.

[ilton]

Hi!

Wich table are you listing Table1, SA or CM_v?

if you are displaying the CM_v consider altering the relationships CM_v -> SA and CM_v -> Table1 crossfilter to both

crossfilter direction