Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Filter across report pages

  • 1.  Filter across report pages

    Silver Contributor
    Posted 17 days ago
    I feel like this should be simple, but I can't figure it out.

    My boss wants a two-page customer report. The first page is a large table (or matrix) with a bunch of details (total sales, number of orders, sales rep, etc.) for each customer. The second page has a bunch of visuals that are supposed to be specific to one customer.

    My boss wants to use the first page to select a specic customer, then use that selection to populate all the visuals on the second page.

    How can I accomplish this? I've created a measure to capture the selected customer, but it doesn't persist when I click on the tab for Page 2:
    Selected Customer = SELECTEDVALUE(customer[customer_number])


    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------


  • 2.  RE: Filter across report pages

    Silver Contributor
    Posted 17 days ago
    I'm already mildly embarrassed for even posting. Seems like the simple solution is to set up Drillthrough. Halfway through a 6-minute video on Guy in a Cube, I had everything I need, and so far, it seems to be working perfectly. I'll post an update if I can't get it to work.
    https://www.youtube.com/watch?v=2x9lLHDbtDk

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 3.  RE: Filter across report pages

    Silver Contributor
    Posted 17 days ago
    Hi,
    you have to define the second page as drillthrough page, setting the drillthrough filter of this page with customer column.
    When you will move on the customer name in the matrix of first page, you could right-click and select drillthrough page.
    You have to hide second page obviously.

    Regards
    Lorenzo

    ------------------------------
    Lorenzo Vercellati
    PowerBI Solution Architect
    SolidQ
    Milan
    3382819866
    ------------------------------



  • 4.  RE: Filter across report pages

    Bronze Contributor
    Posted 16 days ago
    You could also play around with synced slicers depending on your implementation and needs

    https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers#sync-and-use-slicers-on-other-pages

    ------------------------------
    Mike Kromminga
    Data Architect
    ------------------------------



  • 5.  RE: Filter across report pages

    Silver Contributor
    Posted 13 days ago
    Thanks, Mike. Synched slicers was the first thing I looked at, but I need the selection tool to be a table or matrix and could not find a way to synch a selection in that visual with a slicer.

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 6.  RE: Filter across report pages

    Top Contributor
    Posted 12 days ago
    Hi Chris !

    In case you wanted to go through a sync slicer route; you will need to add customer filter on both pages.

    • Now on your Page 1 (Matrix Visual), make sure edit interaction between matrix visual & customer slicer is turned on so either you made selection from matrix or customer slicer selection would be made.
    • Also in your report setting check that you have changed to cross filtering from cross highlighting
    • Check by making some selection from both matrix & selection visual that customer are filtered.
    • Now click on sync slicer check box & select Customer slicer to be sync between pages.
    • Now click on selection check page & hide customer slicer on both pages if you want.

    Note: Drill-through option is more elegant but i provide you instructions for sync slicer method in case you want to give it a try

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------



  • 7.  RE: Filter across report pages

    Silver Contributor
    Posted 12 days ago
      |   view attached
    Thanks, @Hasham Bin Niaz. I actually prefer the cross-filtering. I think it would be more intuitive than drillthrough for the report I'm building.

    But I haven't been able to get it to work. I've attached a simple pbix file to show what I tried to do. There is a three-customer table, a matrix and a slicer. I believe I set Interactions correctly, and I checked the box in Settings to changes "default visual interaction from cross highlighting to cross filtering." Would you mind taking a look? Thank you.​

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------

    Attachment(s)

    pbix
    test_filters.pbix   26K 1 version


  • 8.  RE: Filter across report pages

    Top Contributor
    Posted 11 days ago
      |   view attached
    Hi @Christopher Schnaars,

    You can just copy paste the slicer from one ​page to the other and PBI will automatically ask you if you want to sync slicers.

    If not, then you can enable the Sync Slicer Pane from View Tab. You can toggle if you want the slicer to be visible on the other page or not.

    I am attaching my file.

    Hope this helps.

    Thank you,


    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    test_filters.pbix   27K 1 version


  • 9.  RE: Filter across report pages

    Silver Contributor
    Posted 11 days ago
    Thanks, @Vishesh Jain. The problem isn't getting two slicers across two pages to synch. What I can't figure out is how to allow a user to click a row in a table or matrix and use that selection to filter the slicer on the same page. (I then can synch that slicer with slicers on other pages to pass that selection, as you suggested.)

    My report is a customer detail report. The desired functionality is to select a customer in the table on the first page and use that selection to populate a bunch of customer-specific visuals on two or three other pages.

    Thanks, everyone.​

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 10.  RE: Filter across report pages

    Top Contributor
    Posted 11 days ago
    Oh sorry my bad!

    I didn't understand the problem.

    I'll try to work on it and get back to you.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 11.  RE: Filter across report pages

    Silver Contributor
    Posted 11 days ago
    Ha! But please, no apologies necessary. This community is the best.

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 12.  RE: Filter across report pages

    Silver Contributor
    Posted 8 days ago
    Hi @Christopher Schnaars​,
    have you tried setting a filter for the slicers where a measure has to be greater then 0?

    ------------------------------
    Lorenzo Vercellati
    PowerBI Solution Architect
    SolidQ
    Milan
    3382819866
    ------------------------------



  • 13.  RE: Filter across report pages

    Silver Contributor
    Posted 6 days ago
    Thanks, @Lorenzo Vercellati. This is a brilliant idea, but I wasn't able to get it to work. I applied a measure as a filter on the slicer, but when I clicked on a customer in the table, it had no effect on the slicer. The filter did eliminate customers where the measure value was 0, but it had no additional effect when I selected a customer. Thanks for the idea, though. I was hopeful it would work.​

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 14.  RE: Filter across report pages

    Top Contributor
    Posted 5 days ago
    Hi Chris !

    Thanks for sending over the sample file & your patience.

    Your requirement seems to be a bit tricky, i used a slight different approach by filtering the selected [Matrix] value into [Slicer] filter but still unable to get the Slicer value selected which will trigger the sync slicer on another page.

    I am still working through your requirements as it seems interesting to me & will share a detail solution to you guys.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------



  • 15.  RE: Filter across report pages

    Top Contributor
    Posted 5 days ago
    Hi @Hasham Bin Niaz and @Christopher Schnaars,

    I have been at it as well (unsuccessful so far) and here is my approach​​.

    Can we create a measure that has only 1 value. This value is the one selected from the matrix.
    Then we can put this measure having only 1 value, into the filter pane of the slicer visual and filter the slicer down to that one value, selected in the matrix.

    By using the SELECTEDVALUE() function and putting the measure on the card visual, I am getting the 1 value selected on the card, but I am unable to use this measure to filter the slicer visual.

    It would be great if anyone can figure this out.

    My apologies, if you have already thought of it. Just thought of sharing it in case you hadn't.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 16.  RE: Filter across report pages

    Top Contributor
    Posted 5 days ago
    Edited by Farhan Ahmed 5 days ago
      |   view attached
    In case of slicer is not working, you can use Hierarchy Slicer to achieve it... When you click on table Value it will filter out the Hierarchy Slicer value as well but you need to select it in order for this to work and Hierarchy Slicer can be synced across multiple pages.....

    I will suggest you to go with @Lorenzo Vercellati work by ​going it with Drillthrough.

    In case you want to understand Hierarchy Slicer working I am attaching you your updated file using Hierarchy Slicer..

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------

    Attachment(s)

    pbix
    test_filters.pbix   252K 1 version


  • 17.  RE: Filter across report pages

    Silver Contributor
    Posted 5 days ago
    Is there a uservoice idea submission for this yet? there should be a way to filter across pages besides the drill through (which works extremely well just not the most intuitive)... If everything on a PBI report is supposed to interact with each other why is the slicer one of the only things that is a one way street.

    Is it possible to dynamically assign bookmarks? then create a button to go to that bookmark?

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Indianapolis
    ------------------------------



  • 18.  RE: Filter across report pages

    Silver Contributor
    Posted 5 days ago
    Edited by Christopher Schnaars 5 days ago
    @Hasham Bin Niaz, thank you very much for your efforts. I'm hopeful you can come up with something. I agree with @Sam Duval that this functionality would be more intuitive than drillthrough. I have not submitted this as an idea, but will look into how to do that. The desired functionality would be to go to another page when you select a specific row from a table or matrix. One advantage of drillthrough is you can have multiple pages and pick the one you want.

    @Vishesh Jain, I tried exactly the same approach as you -- using SELECTEDVALUE() -- and also could not get it to filter the slicer either.

    @Farhan Ahmed, I have not tried your file yet but have downloaded it. I'll try it out later today and report back. I've never heard of hierarchy slicers, so I'm eager to see what they are and how they work.

    Thanks so much to all of you for helping me try to figure this out.

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 19.  RE: Filter across report pages

    Silver Contributor
    Posted 4 days ago
      |   view attached
    This appears to work, not the most elegant, but it does the job. Taking advantage of the Power BI URL filtering, create a new column via create column by example. Take the URL for Page 2 of your report from power bi service (assuming you've published it or will publish) This column and append the field value that you want to filter for on page two. There might be away to mask the URL behind a user friendly name, in which case perhaps you could change a person's name to be a URL field that contains this link.

    https://docs.microsoft.com/en-us/power-bi/service-url-filters

    https://docs.microsoft.com/en-us/power-bi/power-bi-hyperlinks-in-tables


    Thanks to whoever it was that posted the test file for me to hijack.

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Indianapolis
    ------------------------------

    Attachment(s)

    pbix
    test_filters2.pbix   254K 1 version


  • 20.  RE: Filter across report pages

    Silver Contributor
    Posted 3 days ago
    Hi, everyone:

    I appreciate all the effort of trying to solve this problem. I spent some time with the test file @Farhan Ahmed uploaded. It gets me closer in that selecting a row in the table filters the slicer, but you still have to check the box in the slicer to select that customer. Still, it was interesting to me to see that Farhan was able to filter a slicer via a table. (I am still not clear on how you managed to do this, Farhan. If you wouldn't mind explaining a bit further, I'd be grateful.)

    The consensus seems to be to stick with drillthrough, which is what I'm doing. To make it a little more user-intuitive, the "title" of my report -- a big textbox at the top of the page -- just says: Right-click a customer, then select Drillthrough

    Thanks again, everyone. I appreciate the conversation.


    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------