Data between datetime table.

[Alexx95]

Hi everyone, I have a problem that I can't find the answer to solve or yes but impossible to solve with pbi sources.
The problem is this.
I have 1 table ( "A" ) with datetime column and another table ( "B" )with 2 datetime columns.
The first one has almost 75 Million of records for only one month (I want to see more than one month in my history) and the second has almost 25k rows (for one month too).

The question is simple and complicated at the same time for the quantity of rows.
I need to show how many records for table "A" have in one record of table "B", the datetime for A should be between 2 datetimes in B.
If this tables were more little i can combine tables and expand, create a condition between and use one filter but I can not duplicate to many rows.
Is there a solution for this case? Of course we have some columns that we can use to relate with both, the same "date" and others than make for every record of A we have almost 8 records for B. Thanks.

[Anonymous]

Hi @Alexx95,

Consider as follows:

• If your data source respond speed is quick, consider using Direct Query mode.
• If you are using sql server, consider using store procedure in sql, then output the result in power bi.
• Also consider Direct Lake mode, which is a semantic model capability for analyzing very large data volumes in Power BI.
• If you have data in power bi, use below measure to check if it works:

RecordCount =
VAR StartDate = SELECTEDVALUE('TableB'[StartDateTimeB])
VAR EndDate = SELECTEDVALUE('TableB'[EndDateTimeB])
RETURN
CALCULATE(
    COUNTROWS('TableA'),
    FILTER(
        'TableA',
        'TableA'[DateTimeA] >= StartDate &&
        'TableA'[DateTimeA] <= EndDate
    )
)

Best regards,

Joyce

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

[Alexx95]

Hi! Thanks for the answer.
1- The response is not quick there. In facts I told that 75kk rows is for only a month but I need more than one to history.
2- I tried to do in sql, but the 2 tables were in 2 different databases and 2 different servers too and can not solve with that.
3- I can try with data lake.
4- this solution is tried but it is the most slow and I do not remember if pbi tell me "out of resources"