Query for one record only with filter

[Anonymous]

Hi,

We have a table visual in Power BI with millions of data rows. When interacting with the table, the end-users are only ever interested in 1 particular record which they want to filter for, ie. they want to see customer information for a particular contract. In SQL they would write

select

customer_column_a,

customer_column_b

from table

where contract_id = 'x'

This can of course be implemented so that all data is loaded and the end-user filters the table for the contract_id. they are interested in. However, is there a way to implement this, with e.g DirectQuery, in a way in which millions of data rows are not loaded? So that only the data for the particular contract the user is interested in (at this particular moment in time) would be loaded. The end-users would be working with Power BI Report Server, so changing report definitions (M or DAX) is not an option.

I understand that the query is very easy to do with an SQL client... We might go this route if there is no easy way to do this in Power BI.

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

I have a little confused about your requirement. 

Do you want to only load the filtered data when get data from SQL Server Database?

If it is, you could type the query under SQL Statement.

Then it will only load the filtered rows based on your sql query.

More details, please refer to this blog_Using T-SQL Queries to Retrieve SQL Server Data.

Best Regards，

Cherry

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Anonymous · Accepted solution]

Hi,

Thanks for the response.

I have solved my issue. What I was looking for using parameters for dynamically changing the SQL query without needing to jump to Query Editor.

Thanks for your help!

[cmontoya88]

Hi, how solved you problem??? Can you use parameter dinamically without power query if not only in the interface of power bi???

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

I have a little confused about your requirement. 

Do you want to only load the filtered data when get data from SQL Server Database?

If it is, you could type the query under SQL Statement.

Then it will only load the filtered rows based on your sql query.

More details, please refer to this blog_Using T-SQL Queries to Retrieve SQL Server Data.

Best Regards，

Cherry