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

MarkS's avatar
MarkS
Icon for Resolver IV rankResolver IV
8 years ago

Hi AGuero

  In the query editor, could you filter the First Manager column=President?  That should give you the VP of that department as the only employee in the department.  To get to your desired result just delete the other columns an Rename the employee column to VP.

 

 

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    In Power Query, just select the rows where First Manager <> null and Second Manager = null

     

    let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each [First Manager] <> null and [Second Manager] = null),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Department", "Employee"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Employee", "VP"}})
in
    #"Renamed Columns"
  • AGuero's avatar
    AGuero
    Frequent Visitor
    8 years ago

    MarkS,

     

    That would work in my simple example, but in reality there are cases where an employee is in a different department than his/her VP. 

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