Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have two tables with Inovicenumber, date and amount.
I compare the two tables, and get a result. But because of minor differences in amounts, the invoices still show up in the list. how can i get this to return only those with amount difference <>1 usd?
My DAX function:
InvoiceDifferences =
VAR __Table1 = SELECTCOLUMNS(Table1, "InvoiceNumber", [InvoiceNumber], "Amount", [Amount], "Date", [Date])
VAR __Table2 = SELECTCOLUMNS(Table2, "InvoiceNumber", [InvoiceNumber], "Amount", [Amount], "Date", [Date])
VAR __Table1Except = EXCEPT(__Table1, __Table2)
VAR __Table2Except = EXCEPT( __Table2, __Table1)
VAR __Result =
FILTER(
UNION(
ADDCOLUMNS( __Table1Except, "Source", "Table1"),
ADDCOLUMNS( __Table2Except, "Source", "Table2")
),
[Amount] <> 0
)
RETURN
__Result
Solved! Go to Solution.
Hi @MegaOctane1 ,
Based on the information you have provided, I have created the following example to help you solve the problem and you can follow the steps below:
1. Append two tables and differentiate the data of the two tables, add an index column.
2. Add two columns.
Table2difference =
VAR _moverows =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER ( 'Table1', 'Table1'[Source] = EARLIER ( Table1[Source] ) )
)
VAR _amount1 =
CALCULATE (
MAX ( 'Table1'[Amount] ),
FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) - _moverows )
)
VAR _difference =
ABS ( 'Table1'[Amount] - _amount1 )
RETURN
_difference
InvoiceDifferences =
VAR _moverows =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER ( 'Table1', 'Table1'[Source] = EARLIER ( Table1[Source] ) )
)
VAR _amount1 =
CALCULATE (
MAX ( 'Table1'[Table2difference] ),
FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) + _moverows )
)
RETURN
IF ( 'Table1'[Source] = "Table2", 'Table1'[Table2difference], _amount1 )
3. Filter results with Flag=1.
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MegaOctane1 ,
Based on the information you have provided, I have created the following example to help you solve the problem and you can follow the steps below:
1. Append two tables and differentiate the data of the two tables, add an index column.
2. Add two columns.
Table2difference =
VAR _moverows =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER ( 'Table1', 'Table1'[Source] = EARLIER ( Table1[Source] ) )
)
VAR _amount1 =
CALCULATE (
MAX ( 'Table1'[Amount] ),
FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) - _moverows )
)
VAR _difference =
ABS ( 'Table1'[Amount] - _amount1 )
RETURN
_difference
InvoiceDifferences =
VAR _moverows =
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER ( 'Table1', 'Table1'[Source] = EARLIER ( Table1[Source] ) )
)
VAR _amount1 =
CALCULATE (
MAX ( 'Table1'[Table2difference] ),
FILTER ( 'Table1', 'Table1'[Index] = EARLIER ( Table1[Index] ) + _moverows )
)
RETURN
IF ( 'Table1'[Source] = "Table2", 'Table1'[Table2difference], _amount1 )
3. Filter results with Flag=1.
Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |