Forum Discussion
Filter to show mutually exclusive results
New Power BI user here. I receive a new member roster weekly. I need to compare the old roster to the new roster and send information to any incoming or outgoing members. I want to see just the rows of incoming and outgoing members.
I took data from two data sources: 01012021 Roster and 01082021 Roster. I selected 01012021 Roster and then Append Queries as New then 01082020 Roster to get all the data into one table.
| ID | last name | first name | File Name |
| AA | Adam | Amarillo | 01012021 Roster |
| BB | Bobby | Brown | 01012021 Roster |
| CC | Charles | Chartruese | 01012021 Roster |
| AA | Adam | Amarillo | 01082021 Roster |
| CC | Charles | Chartruese | 01082021 Roster |
| DD | David | Denim | 01082021 Roster |
| EE | Edward | Ecru | 01082021 Roster |
How do I select/filter just the incoming and outgoing members while ignoring the members that have no status change? This would be an exclusive OR function. My research brought up the UNIQUE formula in Excel, but I could not get it to work in Power BI. The same with IF(Countif)[ID]=1,"Send info.
Am I even on the right track? Any help would be appreciated.
The Power BI DAX equivalent is the EXCEPT function that allows you to compare two tables of the same structure.
Create two table variables, one for each filename, with the IDs of the users.
EXCEPT(new,old) - all users that are new on the roster.
EXCEPT(old,new) - all users that were dropped from the roster.
Hi jwelch176 ,
Besides using EXCEPT() in DAX, you can also try to use merge tables to get the mutually exclusive results:
Remove the last column to get the result table:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
The Power BI DAX equivalent is the EXCEPT function that allows you to compare two tables of the same structure.
Create two table variables, one for each filename, with the IDs of the users.
EXCEPT(new,old) - all users that are new on the roster.
EXCEPT(old,new) - all users that were dropped from the roster.
v-yingjlCommunity Support
Hi jwelch176 ,
Besides using EXCEPT() in DAX, you can also try to use merge tables to get the mutually exclusive results:
Remove the last column to get the result table:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.