Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am using two sheets for information (Separate Data Sources). The purpose of the report would be to search both sheets (Master Sheet Vs Log). The Master sheet against the other Log sheet to determine what Project#'s (Column) are on the Main sheet but not on the other and then produce the list of the Project#'s (Column that are not on the Log Sheet. Any help is greatly appreciated. Thanks
Hwagner
Solved! Go to Solution.
Hi @Hwagner ,
We can achieve this in two different ways:
1. In the Power Query: as @AlexisOlson suggested, using Merge fuction with the Join Kind type selected as Left (Right)-Anti, you can also refer to the links below.
Find Mismatch Rows with Power Query in Power BI
Finding non-matching values between two tables in Power BI
2. Use DAX to implement: I created a sample pbix file(see attachment), please check if it is what you want. Create the following measure to determine if each Project# in master is also displayed in the log sheet. Then apply the measure to the Filters pane to filter the visual.
Flag =
VAR _tab =
EXCEPT ( VALUES ( 'Master'[Project#] ), VALUES ( 'Log'[Project#] ) )
RETURN
IF ( SELECTEDVALUE ( 'Master'[Project#] ) IN _tab, 1, 0 )
You can also refer the following link to create a calculated table to get the Project# list...
Get Records from Table A that are not in Table B
Is there anything else you need help with regarding this post? And if not, could you please mark it as Answered? This will also help others in the community to find this solution easily if they are having the same problem as you. Thank you.
Best Regards
Hi @Hwagner ,
We can achieve this in two different ways:
1. In the Power Query: as @AlexisOlson suggested, using Merge fuction with the Join Kind type selected as Left (Right)-Anti, you can also refer to the links below.
Find Mismatch Rows with Power Query in Power BI
Finding non-matching values between two tables in Power BI
2. Use DAX to implement: I created a sample pbix file(see attachment), please check if it is what you want. Create the following measure to determine if each Project# in master is also displayed in the log sheet. Then apply the measure to the Filters pane to filter the visual.
Flag =
VAR _tab =
EXCEPT ( VALUES ( 'Master'[Project#] ), VALUES ( 'Log'[Project#] ) )
RETURN
IF ( SELECTEDVALUE ( 'Master'[Project#] ) IN _tab, 1, 0 )
You can also refer the following link to create a calculated table to get the Project# list...
Get Records from Table A that are not in Table B
Is there anything else you need help with regarding this post? And if not, could you please mark it as Answered? This will also help others in the community to find this solution easily if they are having the same problem as you. Thank you.
Best Regards
I'd recommend trying an anti-join:
https://docs.microsoft.com/en-us/power-query/merge-queries-left-anti
@AlexisOlson Thank you, this seems to have worked but running a beta test now. I appreciate the help.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
161 | |
129 | |
129 | |
92 | |
91 |