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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
gweeber
Frequent Visitor

Is there a way to limit the drill through list items to only the "matching" item?

I have a report with a summary page and 52 "detail" pages, one page for each client. Not every client has data every month, so the summary page shows the clients who DO have data for a particular month. For example, the summary page for January 2024 has 8 clients listed, not 52. This is working fine.

 

I have added the appropriate field ("client") to the "add drill-through fileds here" area on each of the 52 client pages. I did NOT check any boxes in the drill-through field's list, though. I can't figure out what checking a box does, or, at least it does nothing that I can see in my example.

 

Back to the summary page...when I right-click on any one of the 8 clients listed, I see the Drill Through option fine. The problem is that I see ALL 52 choices in the list that becomes visible when I right-click and select Drill Through.

 

Is there a way to see ONLY the client's detail page listed when I drill through? So, if I drill through on Client A, I would only like to see Client A as an option. Better yet, I'd rather click on Client A in the summary page and be taken directly to Client A's detail page. Is that possible?

1 ACCEPTED SOLUTION
Bipin-Lala
Solution Supplier
Solution Supplier

If you create 52 different detailed pages and use the same field "Client" for drill-through to each page, you will see all 52 pages as options in the drill-through.

 

I guess there can be multiple ways to tackle this and come up with a solution

 

One of the ways is instead of using drill-through to navigate to specific detailed client page, you can use page navigation using a measure and a button.

 

  • Create a table called PageNavigation that maps each client and page name. Means a table of 52 rows.

BipinLala_0-1708384327134.png

 

  • Create a measure called PageSelection, which uses SELECTEDVALUE to get the corresponding value of the page, based on what filter has been applied. In this case, it would be when you select a client on the summary page
PageSelection = SELECTEDVALUE(PageNavigation[Page])
  • Create a button that will be used to navigate to the selected page. We will use Action property of button to use it for page navigation. Turn on Action -> Select Page Navigation -> Click on fx (conditional formatting)
BipinLala_1-1708384902022.png
  • Select the PageSelection measure you created
BipinLala_2-1708384963940.png
  • You can also create a bonus measure to show page name dynamically on button. Apply the below measure on the Style -> Text property of button (conditional formatting)
Go to Page = "Go to Page: " & PageNavigation[PageSelection]
  • Important: Create the 52 detailed client pages. The names of the pages should exactly match the Page value in your table.
  • Voila! Its done.

For example - We select Client 3 in the visual below. This populates the button to show "Go to Page: Client 3 - Detailed". Clicking on this button would take you to the select Client 3 - Detailed page.

BipinLala_3-1708385314098.png
BipinLala_5-1708385354708.png

You can read more about it here. Let me know if you face any issues. ✌️

 

Another way can be to use RLS to restrict page names of clients based on the Client/ UPN logged in. This will filter the page navigation table of the Client for that user only, so they would be able to navigate to that page only.

 

View solution in original post

2 REPLIES 2
Bipin-Lala
Solution Supplier
Solution Supplier

If you create 52 different detailed pages and use the same field "Client" for drill-through to each page, you will see all 52 pages as options in the drill-through.

 

I guess there can be multiple ways to tackle this and come up with a solution

 

One of the ways is instead of using drill-through to navigate to specific detailed client page, you can use page navigation using a measure and a button.

 

  • Create a table called PageNavigation that maps each client and page name. Means a table of 52 rows.

BipinLala_0-1708384327134.png

 

  • Create a measure called PageSelection, which uses SELECTEDVALUE to get the corresponding value of the page, based on what filter has been applied. In this case, it would be when you select a client on the summary page
PageSelection = SELECTEDVALUE(PageNavigation[Page])
  • Create a button that will be used to navigate to the selected page. We will use Action property of button to use it for page navigation. Turn on Action -> Select Page Navigation -> Click on fx (conditional formatting)
BipinLala_1-1708384902022.png
  • Select the PageSelection measure you created
BipinLala_2-1708384963940.png
  • You can also create a bonus measure to show page name dynamically on button. Apply the below measure on the Style -> Text property of button (conditional formatting)
Go to Page = "Go to Page: " & PageNavigation[PageSelection]
  • Important: Create the 52 detailed client pages. The names of the pages should exactly match the Page value in your table.
  • Voila! Its done.

For example - We select Client 3 in the visual below. This populates the button to show "Go to Page: Client 3 - Detailed". Clicking on this button would take you to the select Client 3 - Detailed page.

BipinLala_3-1708385314098.png
BipinLala_5-1708385354708.png

You can read more about it here. Let me know if you face any issues. ✌️

 

Another way can be to use RLS to restrict page names of clients based on the Client/ UPN logged in. This will filter the page navigation table of the Client for that user only, so they would be able to navigate to that page only.

 

Thank you so much! This was so detailed and helpful. However, I couldn't get this to work until I did the following:

  1. I did not create a new, separate table. Instead, I added the "Page" column to my existing dataset table by duplicating the "Client" column in Power Query then renaming the duplicate column "Page". I have only 1 table/dataset in this Report.
  2. I added the PageSelection measure to the existing dataset table.
  3. I added the "Go to Page " measure to the existing dataset table.
  4. When adding the button to the Summary page, I added the PageSelection measure to the Action, the same as you recommended.
  5. For the "Go to Page " measure, I added that like the PageSelection measure in step 4 but in the Style > Text > Text (fx) field instead.

Now it works! I would never have figured this out without your help! Thanks again!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.