Mapping Stores

[Anonymous]

Hi, I have a file that has store site information that I want to map. I have an issue with how to display these in a map for the following reason.

Every store site is a "Test Site" AND that Test Site can also be what is called a "Control Site" that gets compared to a "Test Site"

Think of this as a store site (the Test Site) and it's surrounding competitive stores (The Control Sites) around that Test store site.

A store site can appear in the Control Store Site column for more than one Test Store Site.

Any ideas on how to display this from a map perspective? I do have Lat/Long for each site number is another file. What I want is the ability to select a Test Site and see all the Control Sites for that Test Site with a different coloring for all the Control Sites. I don''t want different colors for each Control Sites. But rather all Test Sites should be 1 color and all Control Sites be another color.

Any ideas?

Here is my file: 

Test Site	Control Site	Test Cluster	Control Cluster
2700039	2709154	2	2
2700039	2701968	2	3
2700039	2700843	2	2
2700039	2700243	2	3
2700039	2700822	2	3
2700039	2708599	2	5
2700039	2702858	2	6
2700039	2700755	2	5
2700039	2703378	2	5
2700039	2702947	2	1
2700039	2700014	2	5
2700039	2701606	2	3
2701149	2701198	6	1
2701149	2700720	6	2
2701149	2703751	6	4
2701149	2701671	6	6
2701149	2702893	6	2
2701149	2701832	6	2
2701149	2703459	6	5
2701149	2701946	6	2
2701149	2701620	6	4
2701149	2701985	6	2
2701149	2705401	6	6
2701149	2703424	6	2
2708746	2703367	2	4
2708746	2703395	2	2
2708746	2701908	2	4
2708746	2741621	2	2
2708746	2741631	2	2
2708746	2701566	2	6
2708746	2701750	2	6
2708746	2703753	2	6
2708746	2703473	2	1
2708746	2706642	2	2
2708746	2709191	2	2
2708746	2701633	2	2
2700990	2709155	1	2
2700990	2709165	1	2
2700990	2701286	1	1
2700990	2701948	1	1
2700990	2705187	1	1
2700990	2708853	1	2
2700990	2741712	1	2
2700990	2701356	1	1
2700990	2701310	1	1
2700990	2703443	1	3
2700990	2701365	1	5
2700990	2706643	1	4
2706654	2701957	6	4
2706654	2741626	6	2
2706654	2701635	6	2
2706654	2705766	6	6
2706654	2703419	6	4
2706654	2703394	6	4
2706654	2706362	6	4
2706654	2702854	6	6
2706654	2701754	6	2
2706654	2708555	6	4
2706654	2703415	6	4
2706654	2709182	6	4
2703743	2706647	5	6
2703743	2703487	5	6
2703743	2703746	5	4
2703743	2700809	5	2
2703743	2741669	5	2
2703743	2706661	5	2
2703743	2700733	5	1
2703743	2708593	5	4
2703743	2701800	5	3
2703743	2708553	5	6
2703743	2701317	5	6
2703743	2703495	5	1
2741665	2741641	4	2
2741665	2709189	4	4
2741665	2741639	4	4
2741665	2705954	4	4
2741665	2703444	4	2
2741665	2708778	4	4
2741665	2741707	4	2
2741665	2700761	4	1
2741665	2701613	4	4
2741665	2701557	4	2
2741665	2708747	4	2
2741665	2708600	4	4

[Greg_Deckler · Accepted solution]

@Anonymous I fixed it like this:

Promo1 = 
ADDCOLUMNS(
    UNION(
        SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])),
        'Promo'
    ),
    "Legend",IF([Test Site] = [Control Site],"Test","Control")
)

Updated PBIX is attached below sig.

[Greg_Deckler]

@Anonymous So that I understand this, for the first row, you want to see all of the other rows (stores) that have a Control Cluster of 2?

[Anonymous]

@Greg_Deckler yes and then for every unique store within the Test Site column.

I was thinking maybe a slicer for Test Site but then that seems like I restrict the overall map a little too much?

[Greg_Deckler]

@Anonymous I would use a disconnected table for your slicer and then create a Complex Selector to use in the Filter pane for your map. The Complex Selector - Microsoft Power BI Community

[Anonymous]

@Greg_Deckler I just realized that what I said about a Test Site can be a Control Site is not true! All Test Sites do not show up as Control Sites and No Control Sites show up as a Test site.

Does this make a solution easier? I am still trying to figure this out.

[Greg_Deckler]

@Anonymous Don't need the disconnected slicer then or the Complex selector. You could create a measure like HASONEVALUE('Table'[Control Site]),1,0) and use that for a color?

[Anonymous]

@Greg_Deckler 

I am still having a hard time simply trying to filter to a test site and just see that test site and the control sites that compete with that test site. Something as simple as this is perplexing me. Can I upload my .pbix?

[Greg_Deckler]

@Anonymous Sure.

[Anonymous]

Thanks @Greg_Deckler

 Click Here 

[Greg_Deckler · Accepted solution]

@Anonymous I fixed it like this:

Promo1 = 
ADDCOLUMNS(
    UNION(
        SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])),
        'Promo'
    ),
    "Legend",IF([Test Site] = [Control Site],"Test","Control")
)

Updated PBIX is attached below sig.

[Anonymous]

@Greg_Deckler How can I now make this legend be able to do color coding like you have already done for me based on Test Site or Control Site (Red & Blue) and introduce Selecting multiple Test Clusters like Cluster 1 and 2. This would then hopefully be able to show 4 different colors?

[Greg_Deckler]

@Anonymous Hmm. Well, Color in ArcGIS can be a measure. So I suppose you in theory do something. I think the problem you will run into is around figuring out the right context. Also, need to get enough color variation. It would be very tricky.  Maybe:

Measure = 
    VAR __TestStore = MAX('Promo1'[Test Site])
    VAR __ControlStore = MAX('Promo1'[Control Site])
RETURN
    IF(ISBLANK(__TestStore),BLANK(),IF(__TestStore = __ControlStore,__TestStore,__TestStore-2700000))

[Anonymous]

@Greg_Deckler How about if we take the original solution you provided below and make a new Column for Test/Control Cluster for color coding? How would I modify this?

Promo1 =

ADDCOLUMNS(

UNION(

SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])),

'Promo'

),

"Legend",IF([Test Site] = [Control Site],"Test","Control")

[Greg_Deckler]

@Anonymous You would modify the part after "Legend". IDK, the issue is getting a different enough color for each thing you want. If the values are similar, Power BI picks similar colors. 

[Anonymous]

@Greg_Deckler 

What I was asking was to instead do the color coding on the Site's like you provided me in the original code, change it to Cluster.

Here is your code for Site coloring:

Promo1 =

ADDCOLUMNS(

UNION(

SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])),

'Promo'

),

"Legend",IF([Test Site] = [Control Site],"Test","Control")

When I try to create a new Promo2 table with the following:

Promo2 =

ADDCOLUMNS(

UNION(

SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])),

'Promo'

),

"Legend",IF([Test Cluster] = [Control Cluster],"Test","Control")

)

It has the same color coding of 2 colors as for site. I was expecting different colors for each different cluster # from 1 to 6?

[Greg_Deckler]

@Anonymous Try:

Promo2 =
ADDCOLUMNS(
UNION(
SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])),
'Promo'
),
"Legend",IF([Test Site] = [Control Site],[Test Cluster],[Control Cluster])
)

[Anonymous]

@Greg_Deckler 

That gives me all 1 color for everything. Here is updated .pbix

Click Here 

[Greg_Deckler · Accepted solution]

@Anonymous Technically it is not. The problem is that the numbers are very close to one another so Power BI is color coding them nearly the same. This is the problem I was referring to. Try this:

Promo2 = ADDCOLUMNS( UNION( SUMMARIZE('Promo',[Test Site],"Control Site",[Test Site],"Test Cluster",MAX('Promo'[Test Cluster]),"Control Cluster",MAX('Promo'[Control Cluster])), 'Promo' ), "Legend",IF([Test Site] = [Control Site],2^[Test Cluster],2^[Control Cluster]) )

[Anonymous]

@Greg_Deckler Okay, I see that now with this new one and the purplish colors. I guess I am going to have to go back to the drawing board on this to see how best to visualize it. Thank you!

[Anonymous]

Hi @Greg_Deckler 

I will work with this.....but it doesn't need to be ARGIS map. I can use any of the mapping options.

[Anonymous]

If you need any help with ArcGIS, drop a line and myself or one of the developer will help out.