Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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

‎04-30-2018 01:58 AM

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

Labels:
Message 1 of 10
1,236 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable
In response to jaco1951

‎04-30-2018 05:49 AM

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

Message 6 of 10
1,247 Views
0
9 REPLIES 9
v-yulgu-msft
Microsoft Employee
Microsoft Employee

‎05-04-2018 10:38 PM

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.
Message 10 of 10
1,169 Views
0
Anonymous
Not applicable

‎04-30-2018 03:40 AM

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

Message 2 of 10
1,225 Views
0
jaco1951
Helper III
Helper III
In response to Anonymous

‎04-30-2018 03:46 AM

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?

Message 3 of 10
1,223 Views
0
Anonymous
Not applicable
In response to jaco1951

‎04-30-2018 04:18 AM

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.

Message 4 of 10
1,211 Views
0
jaco1951
Helper III
Helper III
In response to Anonymous

‎04-30-2018 04:41 AM

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 
Message 5 of 10
1,206 Views
0
Anonymous
Not applicable
In response to jaco1951

‎04-30-2018 05:49 AM

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.

 

Message 6 of 10
1,248 Views
0
jaco1951
Helper III
Helper III
In response to Anonymous

‎04-30-2018 12:21 PM

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

Message 7 of 10
1,192 Views
0
Anonymous
Not applicable
In response to jaco1951

‎05-01-2018 05:24 AM

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

Message 8 of 10
1,185 Views
0
jaco1951
Helper III
Helper III
In response to Anonymous

‎05-09-2018 12:23 AM

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

Message 9 of 10
1,155 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

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.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All