Need help: Adding a category to duplicates based on a previous categorization.

[LNNRTS]

Hey there,
disclaimer: I'm a total newbie to DAX, quite experienced with excel but I thought it's time to automate my data via power pivot (Excel not PowerBI) 😅

I have two tables. "projects" is listing every manager and the projects they're working on. Another table "managers" listing every manager just once incl. further info such as if he's a "top" manager. In the end, I would like to filter my pivot table to show me every project where a "top" manager is NOT involved. As I understand it right, I have to add the information to the main "projects" Table. First I did a simple lookup in the "Projects" table based on the "Managers" table. But now I have to add the info to every row with a project having a "top" manager involved, as shown below in the desired "inclTopMgmt" collum. The original data is much more complex so that there a 100k of "projects".

Glad for every kind of help/hints etc. Thanks in advance!

Projects table with the desired "inclTopMgmt" collum:
Manager         Projects      Lookup(Managers)       inclTopMgmt
A                       PA                                                    true
A                       PB
B                       PA               top                                true
B                       PC               top                                true
C                       PA                                                    true

Managers Table:
Manager       Status
A                   
B                    top
C

[LNNRTS]

Hey @Jihwan_Kim,
thank you already! Unfortunately it didn't really work. Maybe due to the fact that I'm using power pivot (in Excel) and not PowerBI? (therefore I'm also not able to check your file 😕 )
I'm still trying to fix it / understanding your approach - maybe it's due to my other calculated collums .... ? 

[Jihwan_Kim]

Hi, @LNNRTS 

Please check the below picture and the sample pbix file's link down below.

InclTopMgmt Measure =

IF (

ISFILTERED ( Projects[Projects] ),

IF ( MAXX ( Projects, RELATED ( Managers[Status] ) ) = "top", "true", "" )

)

https://www.dropbox.com/s/y483yipsg2tdst5/lnnrts.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM