Forum Discussion

AGuero's avatar
AGuero
Frequent Visitor
8 years ago
Solved

Second to last non blank value in an array?

I have a chart with employees and reporting relationships and am trying to do some analysis by the VP of each department.  My data looks like this:   Department  Employee     First Manager     Seco...
Need Help
  • jthomson's avatar
    jthomson
    8 years ago

    Could try a big nested if, something like:

     

    if([fourth manager]=null,(similar logic checking the third manager),third manager)

     

    It'll look at each column in turn, and when it finds one that isn't blank, returns the previous column

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi AGuero

 

How about a DAX Measure

 

Dept_VP =
LASTNONBLANK ( VALUES ( TableName[Employee] ), TableName[Employee] )

 

AGuero's avatar
AGuero
Frequent Visitor
8 years ago

Thanks Everyone!

 

I wrote a long, nexted IF statement that worked

 

IF(Table[Fourth Level Manager]<>"" && Table[Fourth Level Manager] <> "President", Table[Fourth Level Manager], IF(Table[Third Level Manager]<>""&&Table[Third Level Manager]<>"President",Table[Third Level Manager],IF......