Forum Discussion
Comparing Difference between two columns in two different tables
Hi Anonymous
Please create a dimension table that contains all ticket numbers. This can be created in power query or can be a calculated table using the following dax
Tickets =
DISTINCT (
UNION (
ALLNOBLANKROW ( Table1[Ticket Num] ),
ALLNOBLANKROW ( Table2[Ticket Num] )
)
)
then you apply the following measureDifference =
COALESCE (
SUMX (
VALUES ( Tickets[Ticket Num] ),
CALCULATE (
VAR Val1 =
SUM ( Table1[Value] )
VAR Val2 =
SUM ( Table1[Value] )
RETURN
IF ( Val1 <> BLANK () && Val2 <> BLANK (), Val2 - Val1 )
)
),
"No Match"
)
Then there is other way to make this relationship works by creating a reference table (to just have the unique ticket numbers). to do this:
Go to Power Query (Transform data), home, manage, then choose referece. you are creating a reference table1 based on the ticket number. then remove duplicate. then do the same for table2.
Connect the reference table1 and table1 (This should have one to many relationship), and the mesaure would work.
Regards
Unfortunately it is still many to many relationship (I assume due to Table 1 will have ticket numbers table 2 doesn't have, and vice versa). But I am not entirely sure of the cause. But I do really appreciate the help.