Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
KiranR
Frequent Visitor

Multiple Column Match and Lookupvalue

I have two table, Table1 has a list of employees and Table2 has a list of courses completed by each employee as shown below.

Table1

Employee List
Employee A
Employee B
Employee C
Employee D
Employee E
Employee F

 

Table2

EmployeeCourse NameCourse Date
Employee ACourse 101-02-20
Employee ACourse 202-02-20
Employee ACourse 303-02-20
Employee ACourse 304-02-20
Employee ACourse 305-02-20
Employee BCourse 112-01-20
Employee BCourse 213-01-20
Employee BCourse 314-01-20
Employee BCourse 215-01-20
Employee CCourse 117-01-20
Employee CCourse 218-01-20
Employee CCourse 319-01-20
Employee DCourse 105-01-20
Employee DCourse 206-01-20
Employee DCourse 307-01-20

 

I want to see the latest course date by each course for each employee and blank if a employee dont have a record, something that results like below. Please help.

 

Employee ListCourse 1Course 2Course 3
Employee A01-02-2002-02-2005-02-20
Employee B12-01-2015-01-2014-01-20
Employee C17-01-2018-01-2019-01-20
Employee D05-01-2006-01-2007-01-20
Employee E   
Employee F   
2 ACCEPTED SOLUTIONS
az38
Community Champion
Community Champion

Hi @KiranR 

try to create a new calculated table

 

LastCourseTable = SUMMARIZE(
Table2, Table2[Employee], Table2[Course Name],
"Last Date", MAX(Table2[Course Date])
)

 

then create a relationship Table1 - LastCourseTable 

by Employee coulmn

then create a matrix visual with:

Rows - Table1[Employee List] (set Showitems with no data) in row parameter

Columns - LastCourseTable[Course Name]

Values -  LastCourseTable[Last Date]

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

TheDataMustFlow
Frequent Visitor

You don't really need to create a measure to obtain the desired result.

 

All you need is to create a relationship between table 1 and table 2 on the employee columns, create a matrix visual with:

Rows: Table 1 Employee List

Columns: Table 2 Course Name

Values: Course Date

 

Then you need to select Latest for the values:

 

Annotation 2020-02-08 105846.png

 

Alternatively, you can create a measure to calculate the latest date: Latest Date = Max(Table2[Course Date]) and put it in values instead

 

then, select Show items with no data for the rows:

 

Annotation 2020-02-08 105847.png

 

 and then, remove the subtotals:

 

image.png

 

the end result should be something like this:

 

image.png

 

I suggest that you create a table just for the courses list and link it to Table 2 so that your matrix can also show courses not completed by any employee.

 

I hope this helps.

 

 

 

View solution in original post

4 REPLIES 4
TheDataMustFlow
Frequent Visitor

You don't really need to create a measure to obtain the desired result.

 

All you need is to create a relationship between table 1 and table 2 on the employee columns, create a matrix visual with:

Rows: Table 1 Employee List

Columns: Table 2 Course Name

Values: Course Date

 

Then you need to select Latest for the values:

 

Annotation 2020-02-08 105846.png

 

Alternatively, you can create a measure to calculate the latest date: Latest Date = Max(Table2[Course Date]) and put it in values instead

 

then, select Show items with no data for the rows:

 

Annotation 2020-02-08 105847.png

 

 and then, remove the subtotals:

 

image.png

 

the end result should be something like this:

 

image.png

 

I suggest that you create a table just for the courses list and link it to Table 2 so that your matrix can also show courses not completed by any employee.

 

I hope this helps.

 

 

 

az38
Community Champion
Community Champion

Hi @KiranR 

try to create a new calculated table

 

LastCourseTable = SUMMARIZE(
Table2, Table2[Employee], Table2[Course Name],
"Last Date", MAX(Table2[Course Date])
)

 

then create a relationship Table1 - LastCourseTable 

by Employee coulmn

then create a matrix visual with:

Rows - Table1[Employee List] (set Showitems with no data) in row parameter

Columns - LastCourseTable[Course Name]

Values -  LastCourseTable[Last Date]

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
KiranR
Frequent Visitor

Hi, thanks for providing me with a solution. 

 

Is there a way to create a new table similar to visual matrix?  

 

Thanks

To clarify, you are looking to create a data table that will have the same information as the matrix visual?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.