Superseded Values using Lookup?

[Blytm001]

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.

Name	Course	Expiry	Superseded By:	Expiry Date
Joe Bloggs	Training Course Level 1	01/01/2024
Dave Smith	Training Course Level 2	01/01/2024
Joe Bloggs	Training Course Level 2	01/01/2025

With the above example Level 2 being the higher course I would like it to appear in the Superseded column and its own expiry date in the following column.

Any help would be most welcome!

Thanks

[Daniel29195 · Accepted solution]

@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

[Daniel29195]

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]

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 · Accepted solution]

@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 · Accepted solution]

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]

@Blytm001 

glad it worked out for you .