Hi @prabavathym ,
you can create typable text slicers for a large DirectQuery table in Power BI without preloading the data. The most effective solution involves using a custom visual called Text Filter from the marketplace, creating small disconnected tables to capture user input, and then using a DAX measure to apply the filter dynamically. This approach ensures that a highly efficient SQL query is sent directly to your Oracle database.
First, you'll need to add the "Text Filter" custom visual from AppSource. You can find it by clicking the three dots in the Visualizations pane and selecting "Get more visuals". Once added, you must create two small, disconnected tables to hold the user's input. Use the "Enter data" feature on the Home tab to create a table named IdParameter with a single column IdValue, and a second table named NameParameter with a column NameValue. It's crucial that these tables have no relationships to your main Sales table.
On your report canvas, add two of the new "Text Filter" visuals. For the first, use the IdParameter[IdValue] field, and for the second, use NameParameter[NameValue]. Next, add a standard Table visual and populate it with the id, name, and amt columns from your Sales table. This results table will appear empty initially, which is the correct behavior.
The logic connecting the inputs to the data is handled by a DAX measure. Right-click your Sales table, select "New measure," and enter the following formula. This code captures the typed values using SELECTEDVALUE and returns a value of 1 only when the current row in the Sales table matches both inputs.
Filter Sales Table =
VAR SelectedID = SELECTEDVALUE ( IdParameter[IdValue] )
VAR SelectedName = SELECTEDVALUE ( NameParameter[NameValue] )
RETURN
IF (
NOT ( ISBLANK ( SelectedID ) ) && NOT ( ISBLANK ( SelectedName ) ),
IF (
MAX ( Sales[id] ) = SelectedID && MAX ( Sales[name] ) = SelectedName,
1,
0
),
0
)
To activate this logic, select your results table visual. Drag the [Filter Sales Table] measure into the "Filters on this visual" section of the Filters pane. Set the filter's condition to is 1 and click "Apply filter". Now, the table will remain empty until you type values into both text boxes and press Enter, at which point it will display the matching record.
This method is highly performant because it leverages query folding. The Text Filter visuals do not load millions of records; they are just simple input boxes. When you enter text, Power BI translates the DAX measure into an optimized native SQL query with a precise WHERE clause, ensuring your Oracle database does all the heavy lifting and returns only the specific data you requested.
SELECT "id", "name", "amt"
FROM "Sales"
WHERE "id" = 'user_typed_id' AND "name" = 'user_typed_name'
Best regards,
