Dynamically change where statement from slicer selection

[JLEmlet]

Hello -

We're beginners at Power BI and are running into an interesting problem.  We have a table with over 5.9M rows.  We only need to display one customer at a time which brings the max rows to about 500K for the largest client.  Rather than import all 6M rows into the Power BI query, is there a way the user selects the customer first and then we use that selection to drive the DirectQuery to only return that client? 

Thanks in advance.

JE

[v-yetao1-msft]

Hi @JLEmlet 

I do not quite understand what you mean. As you said , you want to display one customer at a time, is customer a single column of values in your data source ? If yes , you can connect to SQL to load the data and then add a slicer with the field “customer” to filter one customer .

Original data :

Filter data :

Best Regard

Community Support Team _ Ailsa Tao

[JLEmlet]

I'd rather be able to use DirectQuery since the underlying data updates every couple of minutes.  But there are nearly 5m rows and the server is an on-prem server and the PowerBI report is deployed in the cloud. So it takes about a minute for the query to run against the database.  But if the query ran only for the customer selected in the slicer, it would only be 10K rows or so per client returning in just a few seconds.  But that would require the slicer to be able to pass the selection into the DirectQuery.  Is that a possible solution?

[smpa01]

@JLEmlet  what is the data source?

[JLEmlet]

The other line data is in a SQL Server table

[JLEmlet]

Sorry about that, it should have said the underlying data is in a SQL server table.

[ebeery]

@JLEmlet what is the motivation behind not wanting to import all 6M rows?  6M rows is really not very large in terms of what Power BI can handle, and your performance is likely to be significantly better if you are using Import mode versus DirectQuery.

[JLEmlet]

The data in the underlying table updates about every 20 to 30 minutes. So we want the power but I report to Always have the latest data, and we're deploying it to the Power BI website. That only allows a seven updates per day. So we're going for a Live connection to the data source. I guess another option would be to create a tabular cube And have that refresh every time the data refreshes. Any ideas?