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.
Hey Guys.
I have 3 tables.
1 Base table ("a")
2 Data Tables ("b","c")
All of them have a username column.
Now I need 2 new tables with conditional rows:
(1) First new table: Includes only rows where username from "b" is also in "a".
(2) Second new table: Includes only rows where username from "c" is not in "a". (basically c-a)
How can I realize that? For (1) I tried merging queries in query editor but this seems to be buggy?
It shows that 164 rows (usernames) are matching but when I create the table it seems to be done nothing:
Resulting table:
I'm also open to any DAX Logic that creates new tables.
In result I would need two new seperate tables.
Thanks in advance 🙂
Solved! Go to Solution.
Hey @lynnsop ,
I would do the work all in Power Query. The joins that you want are already available and you won't have additional tables in the data model that you don't need.
Please find my example file here:
https://www.swisstransfer.com/d/9387d28f-e05e-436e-9151-3de28ad519fd
For your first case, you only have to do an inner join:
For the second case you have to do a "left anti":
And that should already give you the result you want.
Hey @lynnsop ,
I would do the work all in Power Query. The joins that you want are already available and you won't have additional tables in the data model that you don't need.
Please find my example file here:
https://www.swisstransfer.com/d/9387d28f-e05e-436e-9151-3de28ad519fd
For your first case, you only have to do an inner join:
For the second case you have to do a "left anti":
And that should already give you the result you want.
@lynnsop , Try two new tables
new table =
var _tab = INTERSECT(TableB[username], TableA[username])
return
calculatetable(filter(TableA, TableA[user] in _tab))
new table 2 =
var _tab = except(TableC[username], TableA[username])
return
calculatetable(filter(TableC, TableC[user] in _tab))
Hi @amitchandak
the "intersect" and "except" both seem to require a single table and not column of table. Therefore I get an error when I want to implement this solution
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |