topic Re: Need help: Adding a category to duplicates based on a previous categorization. in DAX Commands and Tips

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

LNNRTS — Mon, 31 May 2021 11:55:05 GMT

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

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

Jihwan_Kim — Mon, 31 May 2021 12:32:26 GMT

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

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

LNNRTS — Mon, 31 May 2021 15:26:09 GMT

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 .... ?