Forum Discussion

Mctwist_720's avatar
Mctwist_720
Regular Visitor
1 year ago
Solved

Power BI - Related / VLOOKUP help

Looking for help - I have two tables data loaded in Power BI:  Table one contains training report data with employee IDs, Training Courses, Training Dates, etc.  Table two contains a roster for all employee IDs and thier names.  A 1-to-many relationship was established based on employee IDs, so i can create a visual to show the names of employees who completed training or have overdue training they have previously taken.  The employees who did not accomplish any training are not shown on the training report.  I want to create a table visual to show those employees who have not completed any training and the course, however I cannot seem to connect anything back to show or filter out employees who have not done training.  Looking to see if there is a way to show this information.  Thank you.

 

Example Table 1 (Training Data)

Employee IDTrainingDate Completed
1Darwin Award Training1 Jan 25
1Safety Training1 Jan 25
2Darwin Award Training

1 Jan 25

6Darwin Award Training1 Jan 24
8Safety Training1 Jan 24

 

Example Table 2 (Employee Roster)

NameEmployee ID
Alfred Pennyworth1
Bruce Wayne2
Peter Parker3
Tony Stark4
Ned Stark5
Han Solo6
Spock7
Link8

 

Ideal Table Visual View:

Employee NameMissing Training
Bruce Wayne

Safety Training

Peter Parker

Darwin Award Training | Safety Training

etc.

 

Need Help
  • TomMartens's avatar
    TomMartens
    1 year ago

    Hey Mctwist_720 ,
    creating this measure:

    missing trainings = 
var allTraining = DISTINCT( ALL( 'Training Data'[Training] ) )
var completedTrainings = VALUES( 'Training Data'[Training] )
var missiingTrainings = EXCEPT( allTraining , completedTrainings )
return

IF( COUNTROWS( missiingTrainings ) = 0,
    "completed all trainings", // replace the string with BLANK() if onyy employess with missing trainings need to be listed
    CONCATENATEX( missiingTrainings , 'Training Data'[Training], "|", 'Training Data'[Training] , ASC )
)

    allows me to create this table visual:

    Hopefully, this provides what you are looking for.

    Regards,
    Tom

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    1 year ago

    Hey Mctwist_720 ,
    creating this measure:

    missing trainings = 
var allTraining = DISTINCT( ALL( 'Training Data'[Training] ) )
var completedTrainings = VALUES( 'Training Data'[Training] )
var missiingTrainings = EXCEPT( allTraining , completedTrainings )
return

IF( COUNTROWS( missiingTrainings ) = 0,
    "completed all trainings", // replace the string with BLANK() if onyy employess with missing trainings need to be listed
    CONCATENATEX( missiingTrainings , 'Training Data'[Training], "|", 'Training Data'[Training] , ASC )
)

    allows me to create this table visual:

    Hopefully, this provides what you are looking for.

    Regards,
    Tom