Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all, I'm trying to create a key/reference table that would have distinct values from columns in other existing tables. Example in the image below:
I've tried a few suggestions in other posts but nothing works quite like this. Any help is appreciated!
Hi @Anonymous
What’s the relationship between Order and Department columns when combining them to the same table? For each column, you can get the distinct values by a measure like:
newTable = DISTINCT(UNION(VALUES(Table1[Order]),VALUES(Table2[Order])))
newTable2 = DISTINCT(UNION(VALUES(Table3[Dept]),VALUES(Table4[Dept])))
This will create a table for Order and Department separately. But I cannot combine them without knowing the relationship between them.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
@Anonymous , how order and department are related
we can union Table 1 and Table 2
And Table3 and Table4. After that only option I see is a cross join
CrossJoin( union(Table1,Table2),union(Table3,Table4))
Refer Dax Join
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Another option is, append and merge in power Query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |