cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fcolossi
Frequent Visitor

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

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?

1 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

Снимок.PNG

4. put new table fields into chart 

 

see my pbix in attach

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
az38
Super User
Super User

@fcolossi 

create a measure like

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
fcolossi
Frequent Visitor

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.

@fcolossi 

could you provide an example?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
fcolossi
Frequent Visitor

Sure:

Store table

StoreCityCountry
ANYUSA
BLAUSA
CLondonUK
DParisFrance

Sales table

StoreAmount
A100
B200
C400
D300

 

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.

@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

Снимок.PNG

4. put new table fields into chart 

 

see my pbix in attach

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

fcolossi
Frequent Visitor

@az38 

 

That did the trick!

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

Thanks a lot.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.