HomeНаука и техникаRelated VideosMore From: Guy in a Cube

Dynamic filtering with Power BI

935 ratings | 81681 views
In this video, Patrick looks at taking URL filtering to the next level. He shows how to use dynamic filtering with Power BI reports. He also shows a neat trick of setting a link image for Web URL fields. Filtering in Power BI: https://powerbi.microsoft.com/en-us/blog/filter-a-report-with-a-url-query-string-parameter/ https://powerbi.microsoft.com/en-us/documentation/powerbi-service-url-filters/ LET'S CONNECT! Guy in a Cube -- https://guyinacube.com -- http://twitter.com/guyinacube -- http://www.facebook.com/guyinacube -- Snapchat - guyinacube -- https://www.instagram.com/guyinacube/ ***Gear*** Check out my Tools page - https://guyinacube.com/tools/
Html code for embedding videos on your blog
Text Comments (196)
Doug Blake (1 month ago)
Great tip.  I wasn't able to get this to work with a report published to an App.  Is this a limitation to this method?
Pratik Futane (1 month ago)
Great !!!
Pratik Futane (1 month ago)
+Guy in a Cube welcome! And as always looking forward for more great videos.. 🙂
Guy in a Cube (1 month ago)
Thanks Pratik! And thanks for watching!
steve taylor (1 month ago)
you guys just kill it! informative! Helpful! but not another monotone boring video! I actually feel like you guys connect with the viewers! Also - love the geeky T's and models in the vids haha!
Omar Martinez (1 month ago)
What would be the best way to set up users to access different levels of data? Based on the information and research I have been doing. I would have to create different BI data sets/reports to view the data. To be specific, this is school district data. Example: Public:General Data (District Summary/Campus Summary) All to view only, High Level Administrative: District, campus, grade, and subject summary, Low level Administrative: Campus, Grade, Teacher and student summary. This is all testing data.
eth3rl0rds (1 month ago)
Yup! Done that but not inside the table but against the created buttons inside the sharepoint! Neat idea!
Charles Darwin (1 month ago)
Thanks so much for this. I have exactly the same need to go from a high to a low level detailed report dynamically as you have described it. Could not be any clearer. Great!!
AHMED JALAL Malik (1 month ago)
awesome video. just one thing to know. I am tried to create a hyper link on count right now I have two columns one is the total count and the other with the URL I want to merge this as a single column
Amele Yoh (2 months ago)
Hej! Is it possible to see the detail information on the same page or different page not in another browser? Thank you!
Jonathan CHANAL (2 months ago)
Very useful, thx !
Shweta Sinha (2 months ago)
Hi Patrick. Thanks for the great videos about Power BI features. I have q on dynamic filtering topic, how can I subscribe to send in mail the filtered page of the report? I tried to use Power BI subscribe feature but it is not sending the page with report filter.
Justin Albert (2 months ago)
So useful!! Thank you for this video!
Darkhan (2 months ago)
Guy in a Cube (2 months ago)
Thanks for watching!
Rick Robshaw (2 months ago)
Very cool! i need to take this a step further (which i will try now that i get the concept). I publish my PBI reports to a Production workspace which then propagates to many different companies using my product with their own version of sql database and their own workspace. So the URL i imagine is going to vary based on which customer is running reports from their PBI online portal. I think (not sure) that your sample would not work as i imagine that long url you copied from one workspace would need to change based on who is running the report? make sense?
Karen Estrada (3 months ago)
Please do it on the Analysis Services
Aneesh Kumar (3 months ago)
Thank u so much for the great idea.
nagesh shastry (4 months ago)
Hi Patrick, You are amazing at explaining the concept, I have a request for you. Any trick or trip for dynamic filter example, I need to see latest month data in a particular page, I have lot's of month data, so how do I restrict it automatically for latest month? Thanks in advance You Rock
megha chandni (4 months ago)
How to filter from country or from different KPI's. Cortana is not filtering with country,made it as geolocation too.
c e (5 months ago)
They finally caught up with you https://youtu.be/dgv5HhHxxe8?t=15m23s
Matt McGwire (5 months ago)
Hey Patrick: Having some trouble creating the dynamic link when the column name is my table has a space and a # (Order #). Do you have a solution to this? Thanks.
ashok kolipaka (3 months ago)
YES! I have the same problem, will be good if you have any solution, Thanks.
Junling Cai (5 months ago)
I like your T shirt
pankaj modi (6 months ago)
Hi bro, can you help me to show multiple cards from one column data source , which will split the total data into individual. Is that possible with power bi?
Andres Monroy (6 months ago)
Great video... now what about a SSAS video is needed!
Hi Patrick, this is awesome! once the dynamic filter report opens , I am able to view all the values related to employees in the filter. How to hide the rest of the filter values?
MrKrishna1609 (6 months ago)
Hi Patrick, i need your help for dynamically hiding or showing the column in matrix table by selecting range in date range slicer.
Alekhya Chunduri (6 months ago)
I have an problem with dynamic filtering when I click the link it goes to detail page. It is in turn acting as a report filter but not a page filter. Is there a way to change that to page filter?
utpal kant kumar (6 months ago)
is it not possible to make dynamic dashboard using SQL query :- Suppose I have one query and have 20 State of data So in this I have to change only #state ID on given query ???
Harshad Patil (6 months ago)
Hi, this is really a great work, but how would I make the opened report chromeless? When I append ?chromeless=1 in URL alongwith ?filter, it doesn't get filtered. Do you have any idea how to make filter and chromeless conditions to work together?
barot dhruv (7 months ago)
How to use dyanmic filter using power BI rest API??
Chaudhry Ali Salam (7 months ago)
u r great mate
Guy in a Cube (7 months ago)
Thanks Chaudhry! 👊 Much appreciated
Eder Rocha (7 months ago)
Great video man you're awesome Power BI master.👏👏👏👏👏
Guy in a Cube (7 months ago)
Thank you so much Eder! Thanks for watching! 👊
Sameer Oza (7 months ago)
I have a requirement , have to restrict per user so that the user can see only his/her data. For e.g. my report has 200 sales person..once I publish and if say salesperson "A" opens that report ,the report should show only his data and not any other sales person.this is to maintain the confidentiality.
Andres Quitian (7 months ago)
SSAS + Power BI are having MANY conflicts, sorting is one of them.... they might say "You know, we can sort Z/A but why is not possible A/Z. Very bad guys. The sorting necesity is something basic for a dashboard, how is posible that for so long they didn`t solve it. unbelievable
Praveen Patil (7 months ago)
@Patrick - How do I make the link work to another application just by passing an ID? I want to show NetSuite Page by clink on the Transaction ID that is shown in the Power BI Report.
isemo24 xxx (8 months ago)
Thanks for the awesome video! Now please SSAS....!
sowmiya dayalan (9 months ago)
Hi, I’m new to power BI. I should build a dashboard on the job market. I have a dashboard on job count by employer(company A,B,C,D,E). The users for these reports are the same 5 companies. When someone from company A sign in to view this report, the initial selection in the filter should be company A. He can add view other company data by selecting them from the filter. Can you please add a video to create this feature? Also, is it possible to have search and multiple selection options in the dropdown filter? Thanks in advance!
Sonia Negondeni (9 months ago)
Great video.. Thanks
Guy in a Cube (9 months ago)
Thanks for watching Sonia!
Muhammad Murad haider (9 months ago)
Can Parameters be used to build the report URL dynamically?
renegade_ace (10 months ago)
can I do this with Azure Table Storage?
Ciprian Teodor Busila (10 months ago)
do you think that it is possible to make dynamic sorting of columns in power bi based on slicer selection?
Yogabalakrishnan Kannan (10 months ago)
Great Video, I tried the same and worked good. When I accessed my report in mobile and clicked the link, it opens web page and signed in again comes back to app and showing same summary page. What should be done to open the page in the mobile report itself and show filtered data in it?
Davis Rogers (10 months ago)
Do you have a solution for Automation with strickly VBA, Access, Excel, Sql Strings, recordsets?
Alex Stein (10 months ago)
Could be very helpful. However, once report is published to services link forces the consumers to buy Pro license.
Aditi Datta (11 months ago)
Can you please explain how you connected the visualization 'Select Person' to the URL filter?
neha khasgiwala (1 year ago)
Can this be done with multiple fields from different tables?
MR.Photoshop (1 year ago)
Sadly the filter parameters are kept when user clicks in another report (tab), so it will make end users confused if they try to do so. Is there a way to clear filter when user clicks in another tab?
Mohammed Ahmed (1 year ago)
man, you are genius
Sagar Chhetri (1 year ago)
hi Patrick...Plz make a video on difference between power bi and power pivot
Cristian Dominguez (1 year ago)
Hi!!!teacher!...I wanted to ask you something related to an advanced dax for counting minutes between start time and end time avoiding weekends and holidays and... considering hours from 9am to 17pm? I did it in excel but no much clue to do it in power BI..any advice would be really well recieved...Greetings and thanks again for your fabulous channel!!!! excel example that is working pretty good is:(=SI(O($B$2<$A$2;B5<A5);0;(DIAS.LAB(A5;B5;HolidayList)-(DIAS.LAB(A5;A5;HolidayList)*SI(RESIDUO(A5;1)>$B$2;1;(MAX($A$2;RESIDUO(A5;1))-$A$2)/($B$2-$A$2)))-(DIAS.LAB(B5;B5;HolidayList)*SI(RESIDUO(B5;1)<$A$2;1;($B$2-MIN($B$2;RESIDUO(B5;1)))/($B$2-$A$2))))*($B$2-$A$2)*24))
Anthony Gramaje (1 year ago)
Awesome! :)
Guy in a Cube (1 year ago)
Thanks for watching!
Paulo Batista (1 year ago)
Great. Sadly the filter parameters are kept when user clicks in another report (tab), so it will make end users confused if they try to do so. Is there a way to clear filter when user clicks in another tab?
Pratik Futane (1 year ago)
Great work.!! pls keep publishing the amazing demos..!!
Guy in a Cube (1 year ago)
Thanks for watching Pratik!
Klein Vu (1 year ago)
Good Job bro! Keep up the good work. I'm using Power BI with SSAS. I implemented your approach and added a column in my SQL database view to store dynamic links. This link passed in parameters to launch a web application in-context to the clicked record. Good Stuff!
adetutu olokunbola (1 year ago)
hi so i'm having some issues with the drillthrough filter first after putting on the drill through on power bi desktop and publishing, it doesn't work, you still need to put on the drill through on the power bi service. now after putting on the drill through filter on the power bi service, everytime you leave the report page and come back to it, the filter goes off. and you have to keep putting it on every single time which is quite annoying. is there a way around this? second when you pin to dashboard ,the drill through doesn't work. meaning it only works on the report page it only works kinda well on the desktop but what's the point on the desktop when i need to show it to someone after publishing. it's quite annoying, is there a way work around this?
Akram Sayadi (1 year ago)
if i want to filter for all and the opposit things like 'ALL' what is the solution ??
Ahmad Reza (1 year ago)
I still don't know how to connect the visualization to the URL, what kind of visualization in your salesperson report titled "Select Person" and how to connect it to the URL filter?
Arya Stark (1 year ago)
Great video. I am looking forward to implement in my upcoming projects. However, I am currently working on a possibility where the employees could only see their own filtered reports, so can we link this dynamic filtering to the RLS feature as well? So no one else could filter the report for employees other than themselves.
nakeishag (1 year ago)
Do you have a video that shows to calculate running annual balances. For example total sales for 2016 and 2017 added together and 2016, 2017 and 2018 etc?
Van M Snyder (1 year ago)
Can we do a VISUALFilter in a URL? I have built URLs to send a user to a "detail' report that shows visuals for data from a single company,from a list of many company. But I want one of the visuals tp show data for all the companies - we can see hos the single company compares to all the other companies for that single KPI
Kumar Ankit (1 year ago)
Excellent work, I was looking for the same. Keep uploading such more videos
Stef VSignup (1 year ago)
Great video! I do seem to encounter a problem that I can't figure out The url I want to use has the following structure: https://../ReportSection1?filter=DIM_CUSTOMER/SOLD_TO_CODE eq '1533049' When I copy past the url and press enter it works fine and gives me the correct response: https://../ReportSection1?filter=DIM_CUSTOMER%252FSOLD_TO_CODE%20eq%20%271533049%27 However when I click on it in power bi it doesn't work and it seems to return a slightly different url. If I copy the url from Powerbi and run it manually then it does work (see scenario above). https://../ReportSection1?filter=DIM_CUSTOMER%252FSOLD_TO_CODE%C2%A0eq%C2%A0%271533049%27 It seems to be due to language encoding as it converts a 'space' to '%20' in scenario1 and to '%C2%A0' in scenario2. Anything you think I can to solve this?
Guy in a Cube (1 year ago)
This is a known issue. My understanding was it was fixed and should have hit production by now. I will follow up with the team on it.
Stef VSignup (1 year ago)
Just as I was explaining it to you I figured out the solution. To get it working for me I had to change the url to remove all spaces out of it!. So I ended up putting '%20' myself in the URL... https://../ReportSection1?filter=DIM_CUSTOMER/SOLD_TO_CODE%20eq%20'1533049' Strange that it gives this error, but at least I have a way around it
Hayden Thomas (1 year ago)
Hey Patrick, this doesn't seem to be working anymore. It looks like the forward slash in the URL for the data set is being encoded twice. (e.g. DataSet%252FColumn eq %27Value%27). I noticed other users facing the same issue here: https://community.powerbi.com/t5/Issues/URL-Filter-not-working-on-report-in-App-Workspace/idi-p/191129. Do you know if there are currently any workarounds for this? I've tried manually encoding %2F for my forward slash, but the system still encoded it again to %252F.
Hayden Thomas (1 year ago)
This issue was fixed by the Power BI team! All is good.
Ricci Bosley (1 year ago)
Great video! Can you tell me how you got the 'employee' box on your report? Also, I want to use this filter tool, but I only have one report. I want all my project managers to be able to click the link by their name to see all data items that roll to their name. In your video you have two reports  - is that a requirement, or can I use this as I just described? thank you!!
Ricci Bosley (1 year ago)
You are a rock star! Thanks for the response. Now my only issue is that within BI web services, when I click the hyperlink it opens another web page, which gives the unfiltered data in a new tab  and then I have to go back to the original tab to view the filtered data.
Guy in a Cube (1 year ago)
Honestly, I haven't tried it with one report. I am going to guess it would work. As far as the Employee here is the DAX that I used: Selected Person = IF(ISFILTERED(Salesperson[Employee]),VALUES(Salesperson[Employee]), "Select Person"). Create as a Measure not Calculated Column.
MUHAMMAD ADIL Khan (1 year ago)
Sir, I have a question. I have added a date field in slicer filter. How can I add a filter to slicer control to show records from last week when the report is first loaded. I query 2 months records from SQL view but I need to display a week record only by default.
MUHAMMAD ADIL Khan (1 year ago)
Hi I need to know once the report is published the whole cache is refreshed with new data or it compares and updates the records in the cache?   https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-directquery-about/ Please read the bold text below DirectQuery connectionsWhen using Get Data in Power BI Desktop to connect to a data source, and you choose DirectQuery, the behaviour of that connection is as follows:During the initial Get Data experience, the source is selected. For relational sources, this means a set of tables are selected and each still define a query that logically returns a set of data. For multidimensional sources like SAP BW, only the source is selected.However, upon load, no data will actually be imported into the Power BI store. Instead, upon building a visual within Power BI Desktop, queries will be sent to the underlying data source to retrieve the necessary data. The time then taken to refresh the visual will depend on the performance of the underlying data source.Any changes to the underlying data will not be immediately reflected in any existing visuals. It is still necessary to Refresh, whereupon the necessary queries will be resent for each visual, and the visual updated as necessary.Upon publishing the report to the Power BI service, it will again result in a Dataset in the Power BI service, just as for import. However, no data is included with that dataset.When opening an existing report in the Power BI service, or authoring a new one, the underlying data source is again queried to retrieve the necessary data. Depending upon the location of the original data source, it might be necessary to configure an On-premises data gateway, just as is needed for Import mode if the data is refreshed.Visuals, or entire report pages, can be pinned as Dashboard tiles. To ensure that opening a dashboard will be fast, the tiles are automatically refreshed on a schedule (for example, every hour). The frequency of this refresh can be controlled, to reflect how frequently the data is changing, and how important it is to see the very latest data. Thus, when opening a dashboard, the tiles will reflect the data as of the time of the last refresh, and not necessarily the very latest changes made to the underlying source. An open dashboard can always be Refreshed to ensure it is up-to-date.
MUHAMMAD ADIL Khan (1 year ago)
Sir, but then where data is kept to refresh report? I searched the internet but couldn't get the answer.
MUHAMMAD ADIL Khan (1 year ago)
Thanks for your prompt reply. Then how direct query handles data for a report. I opened one ticket here http://community.powerbi.com/t5/Desktop/How-Direct-Query-Caches-data-Power-BI-SQL-datasource/m-p/225528#M100385
Guy in a Cube (1 year ago)
Publishing a report will not refresh the dataset. I'm also confused as you are mentioning cache, but talking about DirectQuery. With DirectQuery, we do cache some data for the tiles, but the report data is not cached. You can also now control the refresh interval for tiles when using live connections and DirectQuery.
mayur wadhwani (1 year ago)
Thanks.Great Video..!! For achieving URL(specific to each category to filter dynamically) I found another option as well "Column from Example" in Query Editor pane.
nguoihanoi (1 year ago)
Thanks for the video. I like the way you presented the screen step by step. Could you share what software is used for screen demo like that? I'm working on my own project and finding screen capturing is such a tiresome job.
Pieter Linden (1 year ago)
In PowerBI against a database like AdventureWorksDW, how would you implement a rule like "Salespeople can only see detail records for their own Territory" etc? Can you do that with PowerBI Desktop, or do you need SSAS? I think in SSAS, you'd do something analogous to using SalesPerson[LogonName] = SUSER_NAME(). Can you do the same in PowerBI Desktop without using SSAS or SQL Server as a back end?
Mohamed Rahmatulla (1 year ago)
Hi, I have tried with the same way., But i am not able to get the required result. In the Link i could see the proper link. But, while Click on the link its not applying the filter. i found something here : ?Filter=Table/Column%C2%A0eq%C2%A0'Value' But, if i manually altered the value C2%A0 to 20, i can able to fetch the data with filter., Please help
Hi, I got the same problem! When using the dynamic url link i open the unfiltered detailed report, whereas if manually apply the filter on the url it works. Have you found what the problem is? Thanks!
Cynthia Derosier (1 year ago)
Will this also work if you need to apply multiple filters?
Augusto Chaves (1 year ago)
Would that work the same for embedded reports? Should I just link it to my page hosting the IFRAME and pass the parameter there? Thanks
Guy in a Cube (1 year ago)
You can control filters via the javascript API. So, you could control that within your application where you are embedding the report and do what you need :)
Richard Morris (1 year ago)
Awesome video. Thank You.
Guy in a Cube (1 year ago)
Thanks for watching Richard!
Tobe Ezeife (1 year ago)
Hello Patrick, Awesome Video, Can you explain how you have the Sales person field in the detailed report, set as Select person at default until a link is clicked from the high level report. Thanks
My Expert Agent (1 year ago)
is it possible in Power BI to have on screen filtering with drop down menus, sliders, etc? I see you can do things in the URL but I have a lot of criteria that I want the end user to filter and urls would be hard to manage. This would be in public links or iFrame code on a public website
Vanessa Fernandes (1 year ago)
If the report with URL filtering is opened on the mobile app, this will not really be helpful since the URL will open up in a browser. Is there an alternate way for this, so that the filtered report opens up on the app itself?
Guy in a Cube (1 year ago)
Currently, there is a limitation that the table and field names cannot have a space :(
Vanessa Fernandes (1 year ago)
Thanks for the reply. I have another question: What if the column name by which the report is being filtered, has a space in between? for eg. Product ID, how should the URL with the filter condition look like in this case?
Guy in a Cube (1 year ago)
Unfortunately, there is not :( that is one draw back.
Imad Rehman (1 year ago)
Can you do year over yearcalculation
Atul Panday (1 year ago)
Please share with me some SHP file publication Sample video.
Ad Bo (1 year ago)
..there is no adjacent white/yellow on a real Rubik cube. Subscribed though.
Ion Belulescu (1 year ago)
Hi, Could you please make a video on getting the data In Power BI from SAP BW. It's a more laborious process. Thanks.
Guy in a Cube (1 year ago)
We can try and work on that. I'd first need to get access to an SAP BW instance.
Abhishek Hanotiya (1 year ago)
Hi Patrick, great video, thanks for the demo, this is really cool. I would like to know is it possible to pass page level filter through URL?
Guy in a Cube (1 year ago)
Glad you found your answer!
Abhishek Hanotiya (1 year ago)
never mind I just read some comments below. thanks.
Neville Collins (1 year ago)
HI, I cannot get this to work at all - Can you point out what might be wrong. My URL ends as ReportSection5?filter=CN47MainTbl//ProjectName eq 'ABCD:ABCD:ABC:AB:ABCD', but it just keeps returning the unfiltered report.
Guy in a Cube (1 year ago)
I noticed two slashes between the table name/field name. Is that possibly the reason? Also, the table and field name are case sensitive.
sumanth Vutukuri (1 year ago)
Thanks for the video! I am very new to power BI, my question here is, in order for the detail report to filter the data based on the url selected in high level report first we need to create a parameter which takes the values passed from url and filters the data accordingly in detail level report right?
Guy in a Cube (1 year ago)
It isn't a parameter. it is a filter. Different things. Also, you don't actually have to define a filter within the report. It would know to filter the table/field that was mentioned in the report.
Bhupendra Patel (1 year ago)
Thanks for the video, we have a requirement so that dynamic filtering happens on the same report. The same way it happens for the graphs. This is for a master detail type of relationship between two tables. The master one is call's assigned to a team and details are the comments updated against the call by support person.
Guy in a Cube (1 year ago)
Yeah, a lot of people are asking for that. Power BI doesn't have that functionality yet.
Marcel Lathouwers (1 year ago)
Great! Didn't know this one yet. Opens up lot's of other "deap linking" stuff
Guy in a Cube (1 year ago)
Yes, it does! Thanks for watching!
Chetan K (1 year ago)
Watch My Tutorial on Power BI https://goo.gl/tKvZW5
Guy in a Cube (1 year ago)
Awesome! Thanks for sharing!
Kenny Ruth (1 year ago)
Great post! One thing of importance: The field must be of type string and neither the tablename or fieldname can contain spaces. You can vote up the fix to this issue here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13852461-url-filter-report-field-that-has-a-space
Guy in a Cube (1 year ago)
That is correct. Thanks for calling that out and pointing to the ideas site. I did talk about those in the original filtering video that is linked to in this video.
Estiaan Roets (1 year ago)
Great video Patrick thanks!! Super basic question please. Where do I see or rename my table name in PBI to modify the URL?
Guy in a Cube (1 year ago)
Estiaan Roets (1 year ago)
Thanks Patrick! Probably bad practise on my end but my table and column names had spaces in them. Took them out and it all worked!
Guy in a Cube (1 year ago)
Thanks Estiaan! To rename a table name, you would have to do that within Power BI Desktop or Excel Power Pivot. Basically in the model itself. It can't be done in the service. You can see your table names within the service by just creating a report on the dataset. You will see the field pane at that point.
Alan Jones (1 year ago)
so why, why why do you say notepad is your favorite editor??? That just messes up my thoughts for the whole video :)
Leo Espejo (2 months ago)
Hahahaha same here that comment is a way to put your faith on test or maybe poor Patrick has no other choice (it's like seeing a MS course on edx.org and hear the instructor: "let's do the search on my favorite one: Bing")
Mustafa Biviji (1 year ago)
Thanks. I was just going to post a related question in the community on how do we setup assign/setup "Web URL" for a column/data coming from a SSAS Multidimensional cube in Power BI Desktop (as all the Data modeling options are greyed out). I would greatly if you could show that too along with the setup in SSAS MD in Power BI Desktop (or provide the answer here in the comments), it would really help. Thanks
Amy Peterson (5 months ago)
Is there a solve for this yet? I'm experiencing the same challenge connecting to a SSAS cube and would love to use this functionality. Love your videos! Just subscribed.
Guy in a Cube (1 year ago)
It is on the list to do. Stay tuned!
LifeBeautifulIs (1 year ago)
How you implement same in the PowerBI Desktop
Guy in a Cube (1 year ago)
It is a combination of Power BI Desktop and the service. The problem is the URL. You need to know what the deep link is. That's why Patrick talks about publishing the detail report first. Get the deep link, and then add that to your model and add the link to your overview report.
Adam Harper (1 year ago)
Please do a video on SSAS Tabular!!!! This would be a godsend on the project I am currently on.
Guy in a Cube (1 year ago)
Thanks Adam! Will work on one!
Jay Killeen (1 year ago)
Hmm filters now persist across all tabs so the high level tab will lose its design intent. Not ideal but good to have this option if you need to hack a dashboard. Probably not best from a user interface perspective.
Guy in a Cube (1 year ago)
That's true. There are multiple ways to go about doing it. This is just one possible option. RLS is definitely a more secure route as the end user can't remove the filter and get access to everything. But, this may work well if you are just trying to enable some easy usability from a dashboard perspective, or a basic quick drill through. As with most things, there is more than one way to go about it. Thanks for sharing the RLS approach though! People should definitely look at that approach.
Jay Killeen (1 year ago)
ah ok. But if you have set the Row Level Security and shared the report via a Dashboard then that'll work ok?? Even easier once Reports can be shared directly.
Adam Harper (1 year ago)
It's so that anyone viewing the report can get the detail. If I'm Lily and the report only exists in your personal workspace I would not be able to view my content. You are correct that the filters would work as expected if you are the only one accessing the report and saved it to your workspace.
Jay Killeen (1 year ago)
Also just some more FYI, looks like special symbols will break this functionality such as: 1. Everything after an ampersand (& or &amp;) in the query string will be omitted even if it is inside single ticks 2. If the value you are searching for contains a + it will be encoded to a space %20 even if it is inside single ticks I wouldn't recommend this functionality unless you can handle the bugs
Jay Killeen (1 year ago)
Also I don't get the difference between URLs for a personal workspace vs group? The URLs still behave the same and filtering still works the way you explained. Unless you are saying it will also fix the issue I explained above :/
Michael Shparber (1 year ago)
Excellent! Will it work in On-Prem tech preview?
Guy in a Cube (1 year ago)
This does not work with the current January 2017 Technical Preview of SSRS. We will update on this if things change.
Filippo Teatini (1 year ago)
just subscribed. cast my vote as well for analysis services :)
Guy in a Cube (1 year ago)
Thanks Filippo!
Ntiyiso Mbhalati (1 year ago)
Please do a video on Analysis Services
Guy in a Cube (1 year ago)
Thanks Ntiyiso!
Wyn Hopkins (1 year ago)
Great stuff thanks for sharing
Guy in a Cube (1 year ago)
Thanks for watching Wyn!
Andrew Barch (1 year ago)
Definitely want a video on the SSAS setup for this.
Guy in a Cube (1 year ago)
Thanks Andrew!
amarines2 (1 year ago)
yes ssas please
Guy in a Cube (1 year ago)
Søren Jensen (1 year ago)
Anyway to make it dynamic with multiple dimensions? Like say you filtered the initial report to only show data for a specific month and you also wanted this criteria passed to your detailed report?
Guy in a Cube (1 year ago)
If you can get that baked into the URL, yes. The problem is that from a DAX perspective, we don't have context on what you have selected for filtering. So, that may be difficult. You may be able to get away with it by using the RELATED function in DAX. That may let you concat a field from a related table. You would need to test it to make sure if functions the way you want though. I don't know if it would work the way you are thinking every time.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.