Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Hwagner
New Member

Power Bi to Find Missing Projects

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

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1642663426536.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1642663426536.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlexisOlson
Super User
Super User

@AlexisOlson  Thank you, this seems to have worked but running a beta test now. I appreciate the help. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.