Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
jaco1951
Helper III
Helper III

Join with greater than AND less than to join date time table against events with start and end dates

Hi

 

I have to join two tables with loan data.

 

The strucuter of the tables can be simplified to look like this:

 

Table%Security_boleanTransactionDateAmountSecurityName
Transactions1001_true13.09.20173060451MCD
Transactions1001_true13.10.20173060451MCD
Transactions1000_true05.05.2017256100581BMW
Transactions1000_true04.06.2017256100581BMW
Paymentplan1001_true13.09.20173060451MCD
Paymentplan1001_true12.10.20173060451MCD
Paymentplan1001_true10.11.20173060451MCD
Paymentplan1001_true09.12.20173060451MCD
Paymentplan1001_true07.01.20183060451MCD
Paymentplan1001_true05.02.20183060451MCD
Paymentplan1001_true06.03.20183060451MCD
Paymentplan1001_true04.04.20183060451MCD
Paymentplan1001_true03.05.20183060451MCD
Paymentplan1001_true01.06.20183060451MCD
Paymentplan1000_true05.05.2017256100581BMW
Paymentplan1000_true03.06.2017256100582BMW
Paymentplan1000_true02.07.2017256100583BMW
Paymentplan1000_true31.07.2017256100584BMW
Paymentplan1000_true29.08.2017256100585BMW
Paymentplan1000_true27.09.2017256100586BMW
Paymentplan1000_true26.10.2017256100587BMW
Paymentplan1000_true24.11.2017256100588BMW

 

The first table (Transactions) contains transactions that have been registerred, while the second table (Paymentplan) is a payment plan from start to end.

 

I do not want the payment plan to interrupt with what data that has already be registerred. In other words I only want to join data from Paymentplan where the date is higher than the max date from Transactions in group of each "%Security_bolean".

 

Does anyone know how I can solve this?

 

Br 

Espen

1 ACCEPTED SOLUTION
Anonymous
Not applicable

So I have got a measure that I think will solve your problem.

 

Starting with the table you provided use the following measure

 

Measure = 
IF (
    MAX ( Append1[Table] ) = "Transaction",
    1,
    IF (
        MAX ( Append1[TransactionDate] )
            >= CALCULATE (
                MAX ( Append1[TransactionDate] ),
                ALLEXCEPT ( Append1, Append1[%Security_bolean] ),
                Append1[Table] = "Transaction"
            ),
        1,
        0
    )
)

What this measure does is it outputs 1 or 0 it will output 1 if the type is transaction or if for paymentplan type the transaction date is less than the max transaction type in the group.

 

So to remove the items from the visual just filter out the items with a 0.

 

View solution in original post

9 REPLIES 9
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @jaco1951,

 

Do you want to apply filters with SQL query before loading the entire huge dataset into desktop? If so, please see below screenshot.

1.PNG

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

To clarify, do you want to join or append the two tables as from your example table it seems you want to append them.

Good question, I have been joining them and then deleted duplicates, but that is not a safe method. 

I think I could append them using the same rules regarding dates, but would that be possible to do for each distinct %Security_bolean group?

Anonymous
Not applicable

So if you have the tables

Transaction

%Security_boleanTransactionDateAmountSecurityName
1001_true13.09.20173060451MCD
1001_true13.10.20173060451MCD
1000_true05.05.2017256100581BMW
1000_true04.06.2017256100581BMW

Payment

1001_true13.09.20173060451MCD
1001_true12.10.20173060451MCD
1001_true10.11.20173060451MCD
1001_true09.12.20173060451MCD
1001_true07.01.20183060451MCD
1001_true05.02.20183060451MCD
1001_true06.03.20183060451MCD
1001_true04.04.20183060451MCD
1001_true03.05.20183060451MCD
1001_true01.06.20183060451MCD
1000_true05.05.2017256100581BMW
1000_true03.06.2017256100582BMW
1000_true02.07.2017256100583BMW
1000_true31.07.2017256100584BMW
1000_true29.08.2017256100585BMW
1000_true27.09.2017256100586BMW
1000_true26.10.2017256100587BMW
1000_true24.11.2017256100588BMW

Do you want the two tables appened to 

Table%Security_boleanTransactionDateAmountSecurityName
Transactions1001_true13.09.20173060451MCD
Transactions1001_true13.10.20173060451MCD
Transactions1000_true05.05.2017256100581BMW
Transactions1000_true04.06.2017256100581BMW
Paymentplan1001_true13.09.20173060451MCD
Paymentplan1001_true12.10.20173060451MCD
Paymentplan1001_true10.11.20173060451MCD
Paymentplan1001_true09.12.20173060451MCD
Paymentplan1001_true07.01.20183060451MCD
Paymentplan1001_true05.02.20183060451MCD
Paymentplan1001_true06.03.20183060451MCD
Paymentplan1001_true04.04.20183060451MCD
Paymentplan1001_true03.05.20183060451MCD
Paymentplan1001_true01.06.20183060451MCD
Paymentplan1000_true05.05.2017256100581BMW
Paymentplan1000_true03.06.2017256100582BMW
Paymentplan1000_true02.07.2017256100583BMW
Paymentplan1000_true31.07.2017256100584BMW
Paymentplan1000_true29.08.2017256100585BMW
Paymentplan1000_true27.09.2017256100586BMW
Paymentplan1000_true26.10.2017256100587BMW
Paymentplan1000_true24.11.2017256100588BMW

or do you want them joined together which would look similar to this

%Security_boleanTransactionDateAmountSecurityName%Security_boleanTransactionDateAmountSecurityName
1001_true13.09.20173060451MCD1001_true13.09.20173060451MCD
1001_true13.10.20173060451MCD1001_true12.10.20173060451MCD
1000_true05.05.2017256100581BMW1001_true10.11.20173060451MCD
1000_true04.06.2017256100581BMW1001_true09.12.20173060451MCD
    1001_true07.01.20183060451MCD
    1001_true05.02.20183060451MCD
    1001_true06.03.20183060451MCD
    1001_true04.04.20183060451MCD
    1001_true03.05.20183060451MCD
    1001_true01.06.20183060451MCD
    1000_true05.05.2017256100581BMW
    1000_true03.06.2017256100582BMW
    1000_true02.07.2017256100583BMW
    1000_true31.07.2017256100584BMW
    1000_true29.08.2017256100585BMW
    1000_true27.09.2017256100586BMW
    1000_true26.10.2017256100587BMW
    1000_true24.11.2017256100588BMW

 

The reason I want to clarify this is that there might be an easier way to do it if you want to join however appending will be harder.

I think appending is the best solution. After appeniding the data I would like the following rows to be excluded:

Table%Security_boleanTransactionDateAmountSecurityName 
Transactions1001_true13.09.20173060451MCD 
Transactions1001_true13.10.20173060451MCD 
Transactions1000_true05.05.2017256100581BMW 
Transactions1000_true04.06.2017256100581BMW 
Paymentplan1001_true13.09.20173060451MCDRemoved when appending
Paymentplan1001_true12.10.20173060451MCDRemoved when appending
Paymentplan1001_true10.11.20173060451MCD 
Paymentplan1001_true09.12.20173060451MCD 
Paymentplan1001_true07.01.20183060451MCD 
Paymentplan1001_true05.02.20183060451MCD 
Paymentplan1001_true06.03.20183060451MCD 
Paymentplan1001_true04.04.20183060451MCD 
Paymentplan1001_true03.05.20183060451MCD 
Paymentplan1001_true01.06.20183060451MCD 
Paymentplan1000_true05.05.2017256100581BMWRemoved when appending
Paymentplan1000_true03.06.2017256100582BMWRemoved when appending
Paymentplan1000_true02.07.2017256100583BMW 
Paymentplan1000_true31.07.2017256100584BMW 
Paymentplan1000_true29.08.2017256100585BMW 
Paymentplan1000_true27.09.2017256100586BMW 
Paymentplan1000_true26.10.2017256100587BMW 
Paymentplan1000_true24.11.2017256100588BMW 
Anonymous
Not applicable

So I have got a measure that I think will solve your problem.

 

Starting with the table you provided use the following measure

 

Measure = 
IF (
    MAX ( Append1[Table] ) = "Transaction",
    1,
    IF (
        MAX ( Append1[TransactionDate] )
            >= CALCULATE (
                MAX ( Append1[TransactionDate] ),
                ALLEXCEPT ( Append1, Append1[%Security_bolean] ),
                Append1[Table] = "Transaction"
            ),
        1,
        0
    )
)

What this measure does is it outputs 1 or 0 it will output 1 if the type is transaction or if for paymentplan type the transaction date is less than the max transaction type in the group.

 

So to remove the items from the visual just filter out the items with a 0.

 

Thanks, I have been used to QlikView where one can create SQL load statements and make huge transformations in the scripting module. I guess this is this not possible in Power BI? 

 

These two tables are part of a bigger load and are being joined/merged so that I can't implement a measure in this stage without messing up all the other load processes.

But I will see if I can modify the whole load routine to make it fit.

 

Br Espen

Anonymous
Not applicable

When you import from a sql server, under advanced options you can add a SQL statement. However, using this will disable query folding.

Capture.PNG

Thank you. This might be the thing I was looking for!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.