Relationships between tables with numeric values

[mroberts_troy]

I have mulitple tables which all carry a linear number as the locator for each row.  The numbers locate items along a linear path, and are not a 1:1 relationship (e.g., 0 is start for all, but maybe Table A has features at 2.05, 23.9, 155.2, etc., Table B has features at 3.7, 100.2, 325.3, etc., and Table C has features at 32.1, 88.7, 210.2, etc.).  I want users to be able to use a slicer to display only the rows (from all the tables) that are between those linear numbers (show the features between 75 & 110).

My current solution was to GENERATESERIES between 0 and the maximum of the feature length, with an interval of .01.  This of course created a HUGE table with millions of rows, and takes a very long time to generate.  I then created 1:many relationships between the series table and the independent tables.  I put a slicer on the page and in theory it should work, right? Except that the slicer doesn't seem to actually slice the data.  It's like it only works for the first few thousand, and then just falls apart and only slices the whole thing all at once or not at all.

Is there something I'm doing wrong here?  I can't share the data, but here's a couple screenshots (ignore the number format, I promise you these are decimal numbers using custom formatting):

[mroberts_troy]

Brief update - I used Power Query to generate the table of possible values instead and it works now.  However, because it's generating almost 170M rows, it takes a very long time.  Is there a better way to do this?

[v-zhangti]

Hi, @mroberts_troy 

If you are running a table that is too large, try incremental refreshing. When you publish a Power BI Desktop model to the service, each table in the new dataset has a single partition. That single partition contains all rows for that table. If the table is large, say with tens of millions of rows or even more, a refresh for that table can take a long time and consume an excessive amount of resources.

With incremental refresh, the service dynamically partitions and separates data that needs to be refreshed frequently from data that can be refreshed less frequently. Table data is filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. When initially configuring incremental refresh in Power BI Desktop, the parameters are used to filter only a small period of data to be loaded into the model.

When published to the service, with the first refresh operation, the service creates incremental refresh and historical partitions and optionally a real-time DirectQuery partition based on incremental refresh policy settings, and then overrides the parameter values to filter and query data for each partition based on date/time values for each row.

This is the relevant document, hope to help you: 

https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-overview 

https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-configure 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

[mroberts_troy]

I don't think this pertains to a query which only generates a list of numbers based on pre-defined values.