Filter Data using list or existing table

[cheid_4838]

I have a table of orders where I am calculating on time performance (OTP%).  Within this table is a column called RouteIDs that I am interest in calculating OTP% for.  Instead of pulling in all 700K lines of data for the last couple of years, I would like to filter that data down to only the records that match the 81 routeids.  Is there anyway to filter based on the routeid table I created so it doesn't pull in all 700K records?  The other two options are to manually add all 81 routeids to my SQL statement or go through the routeid column in my base data and manually select each routeid I want to look at.  Thanks for your help.

[Chewdata]

Hey! 

Does your database have a dimension table with the routes in it? 
If so,
1. create a query that gets this dimension table.

2. Filter this table to contain only the 81 routes that you want.

3. Create a Inner join between this dimension table and the Order table.

This approach might keep query folding from breaking, so you don't pull all 700K row into your query.

[DataNinja777]

Hi @cheid_4838 ,

You can filter your dataset in DAX by using either a calculated table or a measure. If you want to create a new table that only contains records matching the RouteIDTable, you can use the following DAX formula:

FilteredOrders = 
FILTER ( 
    Orders, 
    Orders[RouteID] IN VALUES ( RouteIDTable[RouteID] ) 
)

This will generate a table that includes only the orders where the RouteID exists in RouteIDTable. If your goal is to calculate OTP% dynamically without creating a new table, you can use a measure instead:

OTP_Percentage = 
VAR FilteredOrders =
    FILTER ( 
        Orders, 
        Orders[RouteID] IN VALUES ( RouteIDTable[RouteID] ) 
    )
VAR OnTimeOrders =
    CALCULATE ( COUNT ( Orders[OrderID] ), Orders[OnTime] = TRUE(), FilteredOrders )
VAR TotalOrders =
    CALCULATE ( COUNT ( Orders[OrderID] ), FilteredOrders )

RETURN 
    DIVIDE ( OnTimeOrders, TotalOrders, 0 )

This measure filters the Orders table based on the RouteID values found in RouteIDTable, counts the number of on-time orders, counts the total number of filtered orders, and then calculates OTP% by dividing the on-time orders by the total orders. This approach allows the calculation to update dynamically with any changes in RouteIDTable, making it efficient and scalable.

Best regards,

[Deku · Accepted solution]

Best option is to filter in your SQL statement