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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RicFischer
Helper I
Helper I

Lookup values across two tables

I have three tables. Two have data and a third one is a lookup table to help the other two tables relate to each other.

 

In one table, I have a list of instructors with their qualifiations.

 

Table name: InstructorQualifiations

RicFischer_1-1625763565731.png

 

The other table shows when the instructor earned the qualification by satisfactorily completing a specific unit of training.

 

Table name: TrainingProgress

RicFischer_2-1625763689180.png

 

The lookup table correlates the qualification with the unit.

 

Table name: QualsFromUnits

RicFischer_3-1625763754418.png

 

In the InstructorQualifications table, I would like to show if the instructor with that qualification passed the required unit (with a grade of 'S'). The 'InstructorQualifications'[DateAwarded] and the 'TrainingProgress'[Date] being the same or a different is not important. I may analyze a mismatch later, but for now I just need to show whether or not our data indicates the instructor completed the required training to earn that qualification.

 

Conversely, I had a thought last night that it might be cool if I could show, perhaps in the TrainingProgress table, whether the completed course has a matching qualification for that instructor in the InstructorQualifications. Perhaps someone mistakenly deletes the qualification and I wouldn't otherwise know the qualification was removed unless this Power BI report tells me, "Hey, Ric! Ralph did the unit but doesn't have the qualification! You ought to fix that!" 🙂

 

Below is a pbix file for you to play with and figure out how I can do this. In particular, I find I'm stumped at getting values across multiple tables.

 

https://drive.google.com/file/d/1LTugbsAMi5ve4mF0vJpV4J4vqEkrTQtv/view?usp=sharing

1 ACCEPTED SOLUTION
RicFischer
Helper I
Helper I

I've solved my own problem. Cool! Here are the tables with the solution added. The key was to bring over a column from the lookup table so I could do calculations on it in this table. So, in InstructorQualfications, I added Units from the QualsFromUnits table. In TrainingProgress, I added Qualification from the QualsFromUnits table.

 

There might have been a more efficient way to do this, but this was what I found worked.

 

Suggestions, comments, feedback are welcome in an effort to teach me more stuff!

 

Table name: InstructorQualifications

RicFischer_0-1625771340356.png

 

Calculated columns:

Unit =
LOOKUPVALUE (
QualsFromUnits[Unit],
QualsFromUnits[Qualification], InstructorQualifications[Qualification]
)

 

Unit Passed =
CALCULATE (
MAX ( TrainingProgress[Grade] ),
FILTER (
TrainingProgress,
TrainingProgress[Grade] = "S"
&& InstructorQualifications[Name] = TrainingProgress[Name]
&& InstructorQualifications[Unit] = InstructorQualifications[Unit]
)
)

 

Table name: TrainingProgress

RicFischer_1-1625771869325.png

 

Calculated columns:

Qualification =
LOOKUPVALUE (
QualsFromUnits[Qualification],
QualsFromUnits[Unit], TrainingProgress[Unit]
)

 

HasQualification = 
CALCULATE (
MAX ( InstructorQualifications[Qualification] ),
FILTER (
InstructorQualifications,
TrainingProgress[Grade] = "S"
&& TrainingProgress[Name] = InstructorQualifications[Name]
&& TrainingProgress[Qualification] = InstructorQualifications[Qualification]
)
)

 

View solution in original post

3 REPLIES 3
RicFischer
Helper I
Helper I

Unfortunately, my solution didn't work in my real data. It appears I'm not understanding filters and row context well enough. Out of thousands of what should be "S" results for Unit Passed, I got only one. I think it's picking just one person and one unit to find an "S" and is ignoring all other people and units.

My original solution did work. It turned out I had an overabundance of relationships that were confusing the formula.

 

Too many people-identifiers were going directly from table to table and to the person lookup table. I made them all go only to the person lookup table and the formula's results cleaned right up.

RicFischer
Helper I
Helper I

I've solved my own problem. Cool! Here are the tables with the solution added. The key was to bring over a column from the lookup table so I could do calculations on it in this table. So, in InstructorQualfications, I added Units from the QualsFromUnits table. In TrainingProgress, I added Qualification from the QualsFromUnits table.

 

There might have been a more efficient way to do this, but this was what I found worked.

 

Suggestions, comments, feedback are welcome in an effort to teach me more stuff!

 

Table name: InstructorQualifications

RicFischer_0-1625771340356.png

 

Calculated columns:

Unit =
LOOKUPVALUE (
QualsFromUnits[Unit],
QualsFromUnits[Qualification], InstructorQualifications[Qualification]
)

 

Unit Passed =
CALCULATE (
MAX ( TrainingProgress[Grade] ),
FILTER (
TrainingProgress,
TrainingProgress[Grade] = "S"
&& InstructorQualifications[Name] = TrainingProgress[Name]
&& InstructorQualifications[Unit] = InstructorQualifications[Unit]
)
)

 

Table name: TrainingProgress

RicFischer_1-1625771869325.png

 

Calculated columns:

Qualification =
LOOKUPVALUE (
QualsFromUnits[Qualification],
QualsFromUnits[Unit], TrainingProgress[Unit]
)

 

HasQualification = 
CALCULATE (
MAX ( InstructorQualifications[Qualification] ),
FILTER (
InstructorQualifications,
TrainingProgress[Grade] = "S"
&& TrainingProgress[Name] = InstructorQualifications[Name]
&& TrainingProgress[Qualification] = InstructorQualifications[Qualification]
)
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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