Forum Discussion
Mark Differences Between 2 Table
In that case, you can select the relevant columns first from both tables using "SELECTCOLUMNS" function
Then apply the above procedure
i.e.
Calculated Table = VAR firstTable = SELECTCOLUMNS ( Table1, "ID", [ID], "Serial", [Serial], "Name", [Name] ) VAR SecondTable = SELECTCOLUMNS ( Table2, "ID", [ID], "Serial", [Serial], "Name", [Name] ) VAR Common = INTERSECT ( firstTable, SecondTable ) VAR NotCommon = EXCEPT ( DISTINCT ( UNION ( firstTable, SecondTable ) ), Common ) RETURN UNION ( ADDCOLUMNS ( Common, "Unique", "No" ), ADDCOLUMNS ( NotCommon, "Unique", "Yes" ) )
Zubair_Muhammad I forgotten to mention those table have different structure and number of column . Wondering if Intersect can be based on certain column only, with name conversion
e.g. INTERSECT(TABLE1,"Serial Number",[Serial Number]),INTERSECT(TABLE2,"Serial Number",[serialNum1XXX)) ... ... ?
In that case, you can select the relevant columns first from both tables using "SELECTCOLUMNS" function
Then apply the above procedure
i.e.
Calculated Table =
VAR firstTable =
SELECTCOLUMNS ( Table1, "ID", [ID], "Serial", [Serial], "Name", [Name] )
VAR SecondTable =
SELECTCOLUMNS ( Table2, "ID", [ID], "Serial", [Serial], "Name", [Name] )
VAR Common =
INTERSECT ( firstTable, SecondTable )
VAR NotCommon =
EXCEPT ( DISTINCT ( UNION ( firstTable, SecondTable ) ), Common )
RETURN
UNION (
ADDCOLUMNS ( Common, "Unique", "No" ),
ADDCOLUMNS ( NotCommon, "Unique", "Yes" )
)
Syukron, Zubair_Muhammad It almost there ..
But I've 2 more question related to this if you are OK
Current DAX, both variable that compared but how to find unique only from 1 column e.g. Serial Number from both table
The result now have "null" but unable to get in query mode to remove this null . Unable relate the data by having null
Please could you copy paste some data with expected result
If you could copy paste like this....it will save me time in typing
Customer Date Amount Dave 05-01-18 5 Dave 01-09-18 3 Roy 24-02-18 4 Roy 23-08-18 2