Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi There,
I have 2 tables, Table 1 and Table 2 and i am trying to mark items that unique/not in each other table
We can find and list with EXCEPT but how I want to just to mark it down ?
Solved! Go to Solution.
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" ) )
In above example, Shouldn't John be unqiue..since serial doesn't exist in Table 2?
@Zubair_Muhammad , السلام عليكم
Yes you're right, John should be unique and marked as "Yes" too
Wa alaikumus salam
You can use this calculated table
From the Modelling Tab>>New Table
Calculated Table = VAR Common = INTERSECT ( Table1, Table2 ) VAR NotCommon = EXCEPT ( DISTINCT ( UNION ( Table1, Table2 ) ), 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" ) )
Please see the attached sample file as well
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 |
Hi @Zubair_Muhammad , sorry, jugling with other routine just have chance to reply
So if we look at the table below, unique will only marked when serial is duplicating , despite the ID or Name is same . Kindly need your advise
Table 1 | |||
ID | Serial | Name | |
A123 | XXX | John | |
A123 | YYY | Anna | |
B123 | ZZZ | John | |
Table 2 | |||
ID | Serial | Name | |
A123 | XXX | John | |
B123 | VVV | John | |
C123 | QQQ | Marrie | |
Expectation | |||
ID | Serial | Name | Unique |
A123 | XXX | John | No |
A123 | YYY | Anna | Yes |
B123 | ZZZ | John | Yes |
A123 | XXX | John | No |
B123 | VVV | John | Yes |
C123 | QQQ | Marrie | Yes |
Hi @__zhe,
Please new a calculated table and add a calculated column in it.
Table_3 = UNION(Table_1,Table_2) Unique = IF(CALCULATE(COUNT(Table_3[Serial ]),ALLEXCEPT(Table_3,Table_3[Serial ]))>1,"No","Yes")
Best regards,
Yuliana Gu
Hi @v-yulgu-msft table 1 & 2 have different structure, forgotten to mention in the example and also how to nood include null value
Hi @__zhe,
table 1 & 2 have different structure, forgotten to mention in the example and also how to nood include null value
As Zubair_Muhammad mentioned in previous reply, you can use SELECTCOLUMNS to deal with this problem.
Regards,
Yuliana Gu
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |