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: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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,075 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,086 Views
0
9 REPLIES 9
v-yulgu-msft
Employee
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,008 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,064 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,062 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,050 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,045 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,087 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,031 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,024 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
994 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All