Filter a visual on a drill through page with a different value

[fcolossi]

Hello,

Let's say I have a Stores table, with store, city and country and a Sales table, with store and sales and have a drill through page based on the Store field. I want a visual that shows sales of all stores in the same country of the store drilled.

===== edit ====

The visual should show the saled by city within the same country but not as an aggregation.

Is it possible?

[az38]

@fcolossi 

create a measure like

= CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Stores, Stores[Country]) )

[fcolossi]

Thank you for your response. But with your answer I recognize I wasn't very clear about what need. The visual should show the sales amount by all cities within the same country but not aggregated.

[az38]

@fcolossi 

could you provide an example?

[fcolossi]

Sure:

Store table

Store	City	Country
A	NY	USA
B	LA	USA
C	London	UK
D	Paris	France

Sales table

Store	Amount
A	100
B	200
C	400
D	300

If I drill through Store A, for example, I want one bar chart visual that shows two data points: Store A and Store B as they are located in the same country as Store A. But since that visual is on the drill through page, it's only showing the sales of Store A. Not sure if that's more clear. Thanks for the time anyway.

[az38 · Accepted solution]

@fcolossi 

I can offer you the next workaround.

1. create a table

Table = 
ADDCOLUMNS(
	'Sales Table', "Country", 
	CALCULATE(FIRSTNONBLANK('Store Table'[Country],1), FILTER(ALL('Store Table'),'Store Table'[Store]=EARLIER([Store])))
)

2. create  a bridge Country table

bridgeCountry = DISTINCT('Store Table'[Country])

3 create relationships

4. put new table fields into chart 

see my pbix in attach

[fcolossi]

@az38 

That did the trick!

I'm happy it is not as simple as I thought it should be.

Thanks a lot.