Forum Discussion

Blytm001's avatar
Blytm001
Regular Visitor
2 years ago
Solved

Superseded Values using Lookup?

Hi guys,   I'm looking for some help.    I have a training matrix and I would like to set a column to check for the higher level course and display that in a Superseded column.   E.g.   Na...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    Blytm001 

    this is the output : 

     

     

    can you try this code and tell me if it works for you  : 

    expiry datee =
    var get_max_level  =
    MAXX(
        FILTER(
        'Table (11)',
        'Table (11)'[Name] = EARLIER('Table (11)'[Name])
    ),
    'Table (11)'[course level])

    var get_data =  
    FILTER(
        'Table (11)',
        'Table (11)'[Name] = EARLIER('Table (11)'[Name]) && 'Table (11)'[course level] = get_max_level
    )

    return
    SELECTCOLUMNS(
        get_data,
        'Table (11)'[Expiry]
    )


    measure 2 : 
    superseded by =
    var get_max_level  =
    MAXX(
        FILTER(
        'Table (11)',
        'Table (11)'[Name] = EARLIER('Table (11)'[Name])
    ),
    'Table (11)'[course level])

    var get_data =  
    FILTER(
        'Table (11)',
        'Table (11)'[Name] = EARLIER('Table (11)'[Name]) && 'Table (11)'[course level] = get_max_level
    )

    return
    SELECTCOLUMNS(
        get_data,
        'Table (11)'[Course]
    )

     

     
    please note that you need to add a column course level which rank the courses . ( you can add it via power query ) 
     
    hope this helps .
     
    best regards
  • Blytm001's avatar
    Blytm001
    2 years ago

    Thanks bud, I couldn't work out that in a month of Sundays so I've opted with a different, simpler solution I am able to manage, it's not as elequent as your solution by a long way.

     

    So I created a 'Course Level' column that combines the name and the level number of the course:

    Course Level = IF(TrainingDatabase[Course]="B Permit",TrainingDatabase[Name]&3,IF(TrainingDatabase[Course]="C Permit",TrainingDatabase[Name]&2,IF(TrainingDatabase[Course]="D Permit",TrainingDatabase[Name]&1,"x")))

     

    Then a 'Higher Course Level' which looked at the course level comment and looks for the next level up course and name combo:

    Course Higher = IF(TrainingDatabase[Course Level]=TrainingDatabase[Name]&1,TrainingDatabase[Name]&2,IF(TrainingDatabase[Course Level]=TrainingDatabase[Name]&2,TrainingDatabase[Name]&3,IF(TrainingDatabase[Course Level]=TrainingDatabase[Name]&3,TrainingDatabase[Name]&4,IF(TrainingDatabase[Course Level]="x",""))))
     
    Then use my Superceded column to do this:
    Superseceded = MAXX(FILTER(TrainingDatabase,TrainingDatabase[Course Level]=EARLIER(TrainingDatabase[Course Higher])),TrainingDatabase[Course])
     
    And then again to do the expiry date and status:
    SupersecededExpiryDate = MAXX(FILTER(TrainingDatabase,TrainingDatabase[Course Level]=EARLIER(TrainingDatabase[Course Higher])),TrainingDatabase[ExpiryDate])

     

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

Hello Blytm001 ,

in your example, you have 2 rows having training course level 2 with different expiry date,

so which one should appear  ? 

 

best regrads

  • Blytm001's avatar
    Blytm001
    Regular Visitor
    2 years ago

    Where you can see Joe Bloggs twice I would like his Level 1 training row to have the value for his Level 2 and its expiry.

    • Daniel29195's avatar
      Daniel29195
      Community Champion
      2 years ago

      Blytm001 

      this is the output : 

       

       

      can you try this code and tell me if it works for you  : 

      expiry datee =
      var get_max_level  =
      MAXX(
          FILTER(
          'Table (11)',
          'Table (11)'[Name] = EARLIER('Table (11)'[Name])
      ),
      'Table (11)'[course level])

      var get_data =  
      FILTER(
          'Table (11)',
          'Table (11)'[Name] = EARLIER('Table (11)'[Name]) && 'Table (11)'[course level] = get_max_level
      )

      return
      SELECTCOLUMNS(
          get_data,
          'Table (11)'[Expiry]
      )


      measure 2 : 
      superseded by =
      var get_max_level  =
      MAXX(
          FILTER(
          'Table (11)',
          'Table (11)'[Name] = EARLIER('Table (11)'[Name])
      ),
      'Table (11)'[course level])

      var get_data =  
      FILTER(
          'Table (11)',
          'Table (11)'[Name] = EARLIER('Table (11)'[Name]) && 'Table (11)'[course level] = get_max_level
      )

      return
      SELECTCOLUMNS(
          get_data,
          'Table (11)'[Course]
      )

       

       
      please note that you need to add a column course level which rank the courses . ( you can add it via power query ) 
       
      hope this helps .
       
      best regards
      • Blytm001's avatar
        Blytm001
        Regular Visitor
        2 years ago

        Thanks bud, I couldn't work out that in a month of Sundays so I've opted with a different, simpler solution I am able to manage, it's not as elequent as your solution by a long way.

         

        So I created a 'Course Level' column that combines the name and the level number of the course:

        Course Level = IF(TrainingDatabase[Course]="B Permit",TrainingDatabase[Name]&3,IF(TrainingDatabase[Course]="C Permit",TrainingDatabase[Name]&2,IF(TrainingDatabase[Course]="D Permit",TrainingDatabase[Name]&1,"x")))

         

        Then a 'Higher Course Level' which looked at the course level comment and looks for the next level up course and name combo:

        Course Higher = IF(TrainingDatabase[Course Level]=TrainingDatabase[Name]&1,TrainingDatabase[Name]&2,IF(TrainingDatabase[Course Level]=TrainingDatabase[Name]&2,TrainingDatabase[Name]&3,IF(TrainingDatabase[Course Level]=TrainingDatabase[Name]&3,TrainingDatabase[Name]&4,IF(TrainingDatabase[Course Level]="x",""))))
         
        Then use my Superceded column to do this:
        Superseceded = MAXX(FILTER(TrainingDatabase,TrainingDatabase[Course Level]=EARLIER(TrainingDatabase[Course Higher])),TrainingDatabase[Course])
         
        And then again to do the expiry date and status:
        SupersecededExpiryDate = MAXX(FILTER(TrainingDatabase,TrainingDatabase[Course Level]=EARLIER(TrainingDatabase[Course Higher])),TrainingDatabase[ExpiryDate])