Forum Discussion
Calculating numbers from text
Hi there,
I have 3 columns of data in a data set which i need to use to calculate the number of direct reports, the number of indirect reports and also what layer number an individual sits in with regards to the org design.
I have attached a copy example of the data set with the format of the columns and also the formula that i currently use to calculate this in excel. I need to convert the excel formulas to DAX formulas so i can replicate this in Power BI.
Hoping you can help me
| Number of Direct Reports | COUNTIF(Workers Manager,"*"&$Preferred Name&"*") |
| Number of Indirect Reports | COUNTIF(All Managers in Management Chain,"*"&Preferred Name&"*")-Number of Direct Reports |
| Layer No. | (((LEN(All Managers in Management Chain)-LEN(SUBSTITUTE(All Managers in Management Chain,CHAR(10),""))+1)/2)+0.5) |
Thank you
| Preferred Name | Worker's Manager | All Managers in Management Chain |
| Tim Fellow | Paul Scholes | Roger Rabbit Daffy Duck Mike Myers Shirley Temple |
| Mark Wright | Tim Fellow | Roger Rabbit Mike Myers Jose Hernandez Lugo Shirley Temple |
| John Barnes | Kirsty Gallager | Roger Rabbit Daffy Duck Mike Myers Juan Carlos Rangel Lopez Shirley Temple |
2 Replies
Anonymous You might find this helpful: (2) Excel to DAX Translation - Microsoft Power BI Community
Thanks Greg_Deckler but i cannot see anything that helps with my specific need and need assistance with this fairly soon. Cheers anyway
