Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I have to join two tables with loan data.
The strucuter of the tables can be simplified to look like this:
Table | %Security_bolean | TransactionDate | Amount | SecurityName |
Transactions | 1001_true | 13.09.2017 | 3060451 | MCD |
Transactions | 1001_true | 13.10.2017 | 3060451 | MCD |
Transactions | 1000_true | 05.05.2017 | 256100581 | BMW |
Transactions | 1000_true | 04.06.2017 | 256100581 | BMW |
Paymentplan | 1001_true | 13.09.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 12.10.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 10.11.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 09.12.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 07.01.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 05.02.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 06.03.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 04.04.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 03.05.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 01.06.2018 | 3060451 | MCD |
Paymentplan | 1000_true | 05.05.2017 | 256100581 | BMW |
Paymentplan | 1000_true | 03.06.2017 | 256100582 | BMW |
Paymentplan | 1000_true | 02.07.2017 | 256100583 | BMW |
Paymentplan | 1000_true | 31.07.2017 | 256100584 | BMW |
Paymentplan | 1000_true | 29.08.2017 | 256100585 | BMW |
Paymentplan | 1000_true | 27.09.2017 | 256100586 | BMW |
Paymentplan | 1000_true | 26.10.2017 | 256100587 | BMW |
Paymentplan | 1000_true | 24.11.2017 | 256100588 | BMW |
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
Solved! Go to Solution.
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.
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.
Regards,
Yuliana Gu
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?
So if you have the tables
Transaction
%Security_bolean | TransactionDate | Amount | SecurityName |
1001_true | 13.09.2017 | 3060451 | MCD |
1001_true | 13.10.2017 | 3060451 | MCD |
1000_true | 05.05.2017 | 256100581 | BMW |
1000_true | 04.06.2017 | 256100581 | BMW |
Payment
1001_true | 13.09.2017 | 3060451 | MCD |
1001_true | 12.10.2017 | 3060451 | MCD |
1001_true | 10.11.2017 | 3060451 | MCD |
1001_true | 09.12.2017 | 3060451 | MCD |
1001_true | 07.01.2018 | 3060451 | MCD |
1001_true | 05.02.2018 | 3060451 | MCD |
1001_true | 06.03.2018 | 3060451 | MCD |
1001_true | 04.04.2018 | 3060451 | MCD |
1001_true | 03.05.2018 | 3060451 | MCD |
1001_true | 01.06.2018 | 3060451 | MCD |
1000_true | 05.05.2017 | 256100581 | BMW |
1000_true | 03.06.2017 | 256100582 | BMW |
1000_true | 02.07.2017 | 256100583 | BMW |
1000_true | 31.07.2017 | 256100584 | BMW |
1000_true | 29.08.2017 | 256100585 | BMW |
1000_true | 27.09.2017 | 256100586 | BMW |
1000_true | 26.10.2017 | 256100587 | BMW |
1000_true | 24.11.2017 | 256100588 | BMW |
Do you want the two tables appened to
Table | %Security_bolean | TransactionDate | Amount | SecurityName |
Transactions | 1001_true | 13.09.2017 | 3060451 | MCD |
Transactions | 1001_true | 13.10.2017 | 3060451 | MCD |
Transactions | 1000_true | 05.05.2017 | 256100581 | BMW |
Transactions | 1000_true | 04.06.2017 | 256100581 | BMW |
Paymentplan | 1001_true | 13.09.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 12.10.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 10.11.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 09.12.2017 | 3060451 | MCD |
Paymentplan | 1001_true | 07.01.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 05.02.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 06.03.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 04.04.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 03.05.2018 | 3060451 | MCD |
Paymentplan | 1001_true | 01.06.2018 | 3060451 | MCD |
Paymentplan | 1000_true | 05.05.2017 | 256100581 | BMW |
Paymentplan | 1000_true | 03.06.2017 | 256100582 | BMW |
Paymentplan | 1000_true | 02.07.2017 | 256100583 | BMW |
Paymentplan | 1000_true | 31.07.2017 | 256100584 | BMW |
Paymentplan | 1000_true | 29.08.2017 | 256100585 | BMW |
Paymentplan | 1000_true | 27.09.2017 | 256100586 | BMW |
Paymentplan | 1000_true | 26.10.2017 | 256100587 | BMW |
Paymentplan | 1000_true | 24.11.2017 | 256100588 | BMW |
or do you want them joined together which would look similar to this
%Security_bolean | TransactionDate | Amount | SecurityName | %Security_bolean | TransactionDate | Amount | SecurityName |
1001_true | 13.09.2017 | 3060451 | MCD | 1001_true | 13.09.2017 | 3060451 | MCD |
1001_true | 13.10.2017 | 3060451 | MCD | 1001_true | 12.10.2017 | 3060451 | MCD |
1000_true | 05.05.2017 | 256100581 | BMW | 1001_true | 10.11.2017 | 3060451 | MCD |
1000_true | 04.06.2017 | 256100581 | BMW | 1001_true | 09.12.2017 | 3060451 | MCD |
1001_true | 07.01.2018 | 3060451 | MCD | ||||
1001_true | 05.02.2018 | 3060451 | MCD | ||||
1001_true | 06.03.2018 | 3060451 | MCD | ||||
1001_true | 04.04.2018 | 3060451 | MCD | ||||
1001_true | 03.05.2018 | 3060451 | MCD | ||||
1001_true | 01.06.2018 | 3060451 | MCD | ||||
1000_true | 05.05.2017 | 256100581 | BMW | ||||
1000_true | 03.06.2017 | 256100582 | BMW | ||||
1000_true | 02.07.2017 | 256100583 | BMW | ||||
1000_true | 31.07.2017 | 256100584 | BMW | ||||
1000_true | 29.08.2017 | 256100585 | BMW | ||||
1000_true | 27.09.2017 | 256100586 | BMW | ||||
1000_true | 26.10.2017 | 256100587 | BMW | ||||
1000_true | 24.11.2017 | 256100588 | BMW |
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_bolean | TransactionDate | Amount | SecurityName | |
Transactions | 1001_true | 13.09.2017 | 3060451 | MCD | |
Transactions | 1001_true | 13.10.2017 | 3060451 | MCD | |
Transactions | 1000_true | 05.05.2017 | 256100581 | BMW | |
Transactions | 1000_true | 04.06.2017 | 256100581 | BMW | |
Paymentplan | 1001_true | 13.09.2017 | 3060451 | MCD | Removed when appending |
Paymentplan | 1001_true | 12.10.2017 | 3060451 | MCD | Removed when appending |
Paymentplan | 1001_true | 10.11.2017 | 3060451 | MCD | |
Paymentplan | 1001_true | 09.12.2017 | 3060451 | MCD | |
Paymentplan | 1001_true | 07.01.2018 | 3060451 | MCD | |
Paymentplan | 1001_true | 05.02.2018 | 3060451 | MCD | |
Paymentplan | 1001_true | 06.03.2018 | 3060451 | MCD | |
Paymentplan | 1001_true | 04.04.2018 | 3060451 | MCD | |
Paymentplan | 1001_true | 03.05.2018 | 3060451 | MCD | |
Paymentplan | 1001_true | 01.06.2018 | 3060451 | MCD | |
Paymentplan | 1000_true | 05.05.2017 | 256100581 | BMW | Removed when appending |
Paymentplan | 1000_true | 03.06.2017 | 256100582 | BMW | Removed when appending |
Paymentplan | 1000_true | 02.07.2017 | 256100583 | BMW | |
Paymentplan | 1000_true | 31.07.2017 | 256100584 | BMW | |
Paymentplan | 1000_true | 29.08.2017 | 256100585 | BMW | |
Paymentplan | 1000_true | 27.09.2017 | 256100586 | BMW | |
Paymentplan | 1000_true | 26.10.2017 | 256100587 | BMW | |
Paymentplan | 1000_true | 24.11.2017 | 256100588 | BMW |
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
When you import from a sql server, under advanced options you can add a SQL statement. However, using this will disable query folding.
Thank you. This might be the thing I was looking for!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
82 | |
63 | |
53 |