Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
i have 2 identical table (with 2 columns , "account id" - "amount"), and i want to create a third table that make this:
- contains all the "account id" from the first table
- the column amount is equal the amount in the table 2 if there are some values (not zero) otherwise take the value of the table 1
How can i do ? UNION make all the 2 tables together, and i can't use DISTINCT(UNION....).
Thanks for the help.
Solved! Go to Solution.
Hi @ancorte ,
Please try:
First create a new table:
New Table = UNION( EXCEPT( SUMMARIZE('Table 2','Table 2'[Account ID]),SUMMARIZE('Table 1','Table 1'[Account ID])),SUMMARIZE('Table 1','Table 1'[Account ID]))
Then add column:
Ammount = IF(ISBLANK( MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount])), MAXX(FILTER('Table 1',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]),MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]))
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
sorry for my this reply after a lot of time.
Your suggestion is OK, but i have a problem, because in table 1 for example i have all account id with tha date, so for example i have the account id 72/05/010 with an amount at 30/04/2022 and another amount at 30/06/2022
When i create the new column, in fact i need to have a column with an amount with tha date of the table 1 and/or table 2.
So the New Table, has Account id, Amount and date.
What can i do ?
Thanks for very precious help (is the first time that i see the function EARLIER...)
Hi @ancorte ,
Please try:
First create a new table:
New Table = UNION( EXCEPT( SUMMARIZE('Table 2','Table 2'[Account ID]),SUMMARIZE('Table 1','Table 1'[Account ID])),SUMMARIZE('Table 1','Table 1'[Account ID]))
Then add column:
Ammount = IF(ISBLANK( MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount])), MAXX(FILTER('Table 1',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]),MAXX(FILTER('Table 2',[Account ID]=EARLIER('New Table'[Account ID])),[Amount]))
Output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
can you share some sample data?
Proud to be a Super User!
Table1 | ||||
Account ID | Account Name | Amount | Data | Id Origine Dato Contabile |
66/15/025 | COMPONENTI C/ACQUISTI | -10.225,00 | 30/04/2022 | 1 |
66/25/504 | ACQUISTO MATERIALE VARIO | -658.522,00 | 30/04/2022 | 1 |
table 2 | ||||
Account ID | Account Name | Amount | Data | Id Origine Dato Contabile |
64/10/020 | CONT.P/ONERI SOC.E P/COSTO PERS. | 333 | 30/04/2022 | 4 |
66/15/025 | COMPONENTI C/ACQUISTI | 167 | 30/04/2022 | 4 |
66/25/504 | ACQUISTO MATERIALE VARIO | - | 30/04/2022 | 4 |
66/25/507 | ACQUISTO BENI E MAT PER ATTREZZA | 667 | 30/04/2022 | 4 |
66/30/007 | MATER.MANUT.BENI PROPRI(LIM.5%) | - | 30/04/2022 | 4 |
66/30/015 | MATERIALE DI PULIZIA | - | 30/04/2022 | 4 |
If in table 1 for the same account ID (with the same date) there is a value different from " ", i want that in new table Amount 2 has the value of Table 2 ; instead for the same account ID (with the same date) has a value equal " " i want that in new table Amount 2 has the value of the corresponding row in Table 1.
In the example for account id 66/15/025 in Table1 there is a value in Table 2 : so the new table have to take the value in Table 2 (= 167)
In the example for account id 66/25/504 in Table1 there is a value in Table 2 equal to " ": so the new table have to mantain the value in Table 1 (= -658.522)
I hope that it's clear. Thanls for your help.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |