Retrieve a subset based on user selection

[mentallaeid]

Hello,

I am working on a very large dataset (approx. 20GB) on SQL and I was wondering if it is possible to retrieve a subset of this dataset based on user selection in Power BI. For example, if the user selects ID number = 1 in a slicer, I want to be able to get all the rows from my SQL database that have ID number = 1.

Any help would be appreciated.

Regards,

[smpa01]

@mentallaeid I ran into same issue a while ago where I had to put a dynamic filter into SQL custom query based on a parameter specified by me. You can check the following link.

https://community.powerbi.com/t5/Desktop/POWER-Query-SQL-Statement-Dynamic-Filtering-based-on-a-diff...

[mentallaeid]

@parry2k @smpa01 Thank you for your quick answers.

And is there a way to dynamically change the parameter's value or any other way of retrieving the subset? I need to be able to do that without editing the query.

Regards,

[parry2k]

@mentallaeid I have answered similar question few days ago, in one of the reply on this post, solution is attached. You can use the similar approach to make it dynamic.

Basically you can have parameters in an excel sheet and then you can use that excel sheet to filter your query.

[mentallaeid]

@parry2kI don't think that will work in my case. Maybe I wasn't very clear on what I want to do. Basically, I created an histogram visual using Direct Query and based on this visual I want to select one or several IDs (on a slicer) which will then filter a line chart so that I can analyze my data on a more detailed level. The issue here is that I only need a subset of my large dataset to visualize my line chart, therefore I want to retrieve the subset of the IDs I selected to be able to get my visualization. My idea was then to use the selected IDs to filter the data in an SQL query and retrieve the subset as a result. Is that possible with Power BI desktop? Does your suggestion solve this problem? I'm pretty new to Power BI so if I didn't understand your approach and it actually solves my problem please let me know 🙂

[parry2k]

Afaik it is not possible. Wish you had mentioned this In your initial post.

[mentallaeid]

@parry2koh.. thank you for your help though 

[parry2k]

@mentallaeid you can achieve this using by creating parameter but not thru slicer.