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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
watercress
New Member

Dillthrough Showing All Values in Dimension Table

Hi, been trying to figure out this for many days..

I have unpivoted a main table, and duplicated the table to create a dimension table by removing the duplicates from the Attribute column (cities) and other colums. the 2 tables related through single direction. However when i drillthrough from my main visual, it showed all the cities in the dimension table instead of showing the cities selected for the country. The value of X-Axis = Attribute column of dimension table.

 

For example, when i select USA for drillthrough, i see

San Francisco - correct

New York - Correct

Utah - correct

London - i dont want to see this

Manchester - i dont want to see this

Madrid - i dont want to see this

Tokyo - I dont want to see this

 

Many thanks in advance!

 

1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

Hi, @watercress 

Based on your information, I create a sample table:

vyohuamsft_0-1736930296044.png

vyohuamsft_1-1736930305012.png

Create relationship:

vyohuamsft_6-1736931552080.png

 

Then create tables in page 1 and page 2, like this:

vyohuamsft_2-1736930418995.png

 

vyohuamsft_3-1736930433096.png

 

Create a drillthrough in Page 2 and add country drillthrough field

vyohuamsft_5-1736930503559.png

 

Drill through USA in page 1 and skip to page 2 to show only the cities in USA

vyohuamsft_7-1736931577201.png

vyohuamsft_12-1736932201873.png

 

In operation, I seem to have found your problem. When I set the relationship to single, after I drilled through, nothing happened to the second table

vyohuamsft_9-1736931723463.png

vyohuamsft_10-1736931741437.png

 

Drilling through from country to dimension table, the relationship of the primary table should be passed to the dimension table, but the actual relationship is indeed passed to the primary table. So just set the relationship pass to Both. If your table relationship is only one-way, you can also add the contents of the primary table to the table on the second page, so that you can also get the city of the country you drilled into

vyohuamsft_11-1736932077745.png

 

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yohua-msft
Community Support
Community Support

Hi, @watercress 

Based on your information, I create a sample table:

vyohuamsft_0-1736930296044.png

vyohuamsft_1-1736930305012.png

Create relationship:

vyohuamsft_6-1736931552080.png

 

Then create tables in page 1 and page 2, like this:

vyohuamsft_2-1736930418995.png

 

vyohuamsft_3-1736930433096.png

 

Create a drillthrough in Page 2 and add country drillthrough field

vyohuamsft_5-1736930503559.png

 

Drill through USA in page 1 and skip to page 2 to show only the cities in USA

vyohuamsft_7-1736931577201.png

vyohuamsft_12-1736932201873.png

 

In operation, I seem to have found your problem. When I set the relationship to single, after I drilled through, nothing happened to the second table

vyohuamsft_9-1736931723463.png

vyohuamsft_10-1736931741437.png

 

Drilling through from country to dimension table, the relationship of the primary table should be passed to the dimension table, but the actual relationship is indeed passed to the primary table. So just set the relationship pass to Both. If your table relationship is only one-way, you can also add the contents of the primary table to the table on the second page, so that you can also get the city of the country you drilled into

vyohuamsft_11-1736932077745.png

 

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ritaf1983
Super User
Super User

Hi @watercress 

Please share sample data (excluding sensitive information) in text format, along with your expected result and any relevant logic. For guidance, refer to

How to provide sample data in the Power BI Forum

If possible, upload a simplified .pbix file using this guide:

 How to upload PBI in Community

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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