Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
az38
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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.

az38
Community Champion
Community Champion

@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.

az38
Community Champion
Community Champion

@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
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.