Forum Discussion
Helper ILookup 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
The other table shows when the instructor earned the qualification by satisfactorily completing a specific unit of training.
Table name: TrainingProgress
The lookup table correlates the qualification with the unit.
Table name: QualsFromUnits
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
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
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
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]
)
)
3 Replies
- RicFischer
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
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
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]
)
) - RicFischer
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.
- RicFischer
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.