Using ArcGIS Map to display BOTH retail sales OR ecommerce sales locations simultaneously.

[mbohling]

Hello,

As mentioned in the subject, I am looking to use the ArcGIS map to display sales data for two different groupings.  Sales representatives sell products to retail locations across the country.  I'd like to show these locations ALONG with Ecommerce sales data we are also able to pull in to the project.   

The image below gives an example of what I am looking for.  Here I have selected a salesperson ("Salesperson A") who sells to the locations denoted by blue diamonds.  The purple dots denote Ecommerce sales on the same map.

What's the problem:
In Power BI, slicers default to using AND logic - that is if I were to select Salesperson A from a slicer, and then select www.website1.com from another to show both sets of sales numbers, nothing will appear.  This is because there are no sales that exist where Salesperson A is the salesperson and the Account is www.website1.com.  Our sales reps do not sell to websites.

So I need to achieve OR logic using multiple slicers.  That is, show me all sales that either Salesperson A sold to retail locations OR ecommerce sales from www.website1.com.

Below is a link to the PBIX file with the basic data model and schema I am working with.

https://www.dropbox.com/t/zyE1XSWxajxAFpWA

There are a few pages - they should be pretty straightforward:

1. Working Example of Salesperson B Map

This page shows a map view where I filtered to show sales locations for Salesperson B. (Note retail locations are blue diamonds) This is correct.

2. Working Example of website1.com Map

Identical to the first page except now I've filtered the map to show sales locations for ecommerce sales generated from website1.com. (Note Ecom sales locations are small purple circles).  This is correct.

3. Non-Working Map

This shows an example of a map view with option to slice and filter the data by both Salesperson AND by account.  Here I select Salesperson B as I did in the first page, however because they do not have any sales linked to website1.com there is no way to show that data on the map on this page.  (This is expected behavior and where the OR logic is necessary)

This is where I used a trick I've read about using Disconnected Tables with an OR filter.  I create disconnected tables (they have no relationship to any fact or dimension tables in my model) as follows:

- Disconnected Salesperson (simply a disconnected copy of my Dim Salesperson table)

- Disconnected Account Ecom (a disconnected copy of my Dim Account table filtered to just Ecom accounts)

- Disconnected Account Retail (a disconnected copy of my Dim Account table filtered to just Retail accounts)

On the next page:

4. Working Table Selection

I am able to attach slicer to those tables and the table visual I have on the page is exactly what I'm looking for.  I've selected Salesperson B AND I've selected website1.com.

As you can see the table shows entries for both Salesperson B OR website1.com.

I also used this DAX function to attach as a filter and only show results if the DAX function returns 1:

OR Filter = 
IF (
    OR (
        SELECTEDVALUE ( 'Dim Salesperson'[Salesperson] )
            IN ALLSELECTED ( 'Disconnected Salesperson'[Salesperson] ),
        SELECTEDVALUE ( 'Dim Account'[Account Name] )
            IN ALLSELECTED ( 'Disconnected Account Ecom'[Account Name] )
    )
        && OR (
            SELECTEDVALUE ( 'Dim Account'[Account Name] )
                IN ALLSELECTED ( 'Disconnected Account Retail'[Account Name] ),
            SELECTEDVALUE ( 'Dim Account'[Account Name] )
                IN ALLSELECTED ( 'Disconnected Account Ecom'[Account Name] )
        ),
    1,
    0
)

Essentially if this DAX function is satisfied - the entry will show up on the visual I attach it to.

Finally the last page is what I cannot get to work.  It should just be the exact same logic as page 4, however instead of a table visual I am using a ArcGIS map to plot the data by Zip/Postal Codes:

5. Combined Map View

It seems to completely ignore my slicer selections and just show all sales data from my tables.  I cannot figure this part out.

For some extra context - the point of this is to be able to view our geographical sales data, filter by our retail sales reps and compare those locations they sell to with locations our customers may be according to sales generated by our ecommerce division.

I hope this is clear and I'm very happy to add any more information I can!

[mbohling]

Hi @v-yiruan-msft 

Thanks for your reply.  Apologies for the bad link - I'm not sure why it's not working as I tested it with others and they were able to download the .pbix file from the dropbox link.  For some reason I cannot directly include my .pbix on my post.  So I'll try to figure that out.

In the meantime, to answer your questions 

I did apply the same OR Filter DAX function to the ArcGIS map.  (In the same way I added it to the table visual on Page 4).

Perhaps, it would be helpful to add a screenshot atleast for each of the pages I described in the original post.

1. Working Example of Salesperson B Map

This page shows a map view where I filtered to show sales locations for Salesperson B. (Note retail locations are blue diamonds) This is correct.

2. Working Example of website1.com Map

Identical to the first page except now I've filtered the map to show sales locations for ecommerce sales generated from website1.com. (Note Ecom sales locations are small purple circles).  This is correct.

3. Non-Working Map

This shows an example of a map view with option to slice and filter the data by both Salesperson AND by account.  Here I select Salesperson B as I did in the first page, however because they do not have any sales linked to website1.com there is no way to show that data on the map on this page.  (This is expected behavior and where the OR logic is necessary)

4. Working Table Selection

I am able to attach slicer to those tables and the table visual I have on the page is exactly what I'm looking for.  I've selected Salesperson B AND I've selected website1.com.

As you can see the table shows entries for both Salesperson B OR website1.com.  This is using the OR Filter DAX function in the original post along with the disconnected tables described in the original post.

Finally the last page is what I cannot get to work.  It should just be the exact same logic as page 4, however instead of a table visual I am using a ArcGIS map to plot the data by Zip/Postal Codes:

5. Combined Map View

It seems to completely ignore my slicer selections and just show all sales data from my tables.  Even though I'm filtered on Salesperson B and website1.com it is simply showing all my data on this map UNLIKE the data from the table visual previously that respects my slicer selection choices.

Let me also include the simple data model:

I am plotting the same data used in the table visual by the Zip field found in the Dim Shipping Zip table.

Once again I will try and figure out a way to share the .pbix file but until then let me know if there is anything else I can do to clarify my question

[v-yiruan-msft]

Hi @mbohling ,

I can't download your shared pbix file. Base on your description, it seems like you want to achieve OR function. And you create a measure by DAX to filter the data, it works in other visuals other than ArcGIS Map. Did you apply the same filter on ArcGIS Map? Could you please provide the related settings of ArcGIS Map? 

Best Regards