Simple table visualization using data from four related tables

[dougford99]

GOAL
Using a table visualization, display a list of classes recommended to for an employee based upon their job title, exc;luding the classes they've already attended.

The visualization will be filtered by employee full name and used by managers to determine which classes an employee should register to attend in the future.

Class Title	Recommendation
Networking	Recommended
Security	Required
Virtualization	Recommended
Windows Server Management	Optional

MODEL

dmEmployees: A unique list of employees

• Full name
• Email (primary key)
• Job Title (foreign key for dmJobTitles relationship)

dmJobTitles: A list of unique job titles

• Job Title (primary key)

Job Title

Desktop Analyst

Systems Engineer

Sr. Systems Engineer

dmClassRecommendations: A list of recommendations by job title containing the following fields:

• Job Title (foreign key for dmJobTitles relationship)
• Class
• Recommendation (Required, Recommended, Optional, Not Recommended)

Job Title, Class, Recommendation

Desktop Analyst, Security, Recommended

Desktop Analyst, Networking, Recommended

Systems Engineer, Security, Required

Systems Engineer, Messaging, Optional

Systems Engineer, Networking, Recommended

Systems Engineer, SAN Storage, Optional

Systems Engineer, Virtualization, Recommended

Sr. Systems Engineer, Security, Required

Sr. Systems Engineer, Messaging, Recommended

Sr. Systems Engineer, Networking, Required

Sr. Systems Engineer, SAN Storage, Recommended

Sr. Systems Engineer, Virtualization, Required

ftClassRegistration: A list of classes that an employee has registered to attend

• Name: Name of employee
• Student Email (foreign key for dmEmployees relationship)
• Title: Class title
• Status: Active Enrollment, Pending, Completed

I've managed to create a list of class recommendations, but I can't figure out how to exclude a class if the employee has already attended it.

Thank you in advance for your expertise. The solution to this problem is going to unlock a log jam in my brain that will really help me improve both data modeling and visualizations.

Doug

[v-ljerr-msft · Accepted solution]

Hi @dougford99,

I've managed to create a list of class recommendations, but I can't figure out how to exclude a class if the employee has already attended it.

---

If I understand you correctly, you should be able to use the formula below to create new measure, then use it to apply a visual level filter(measure is 1) on Table visual of class recommendations list you have managed to create, to exclude a class if the employee has already attended it. 

measure =
VAR currentClass =
    MAX ( dmClassRecommendations[Clas] )
RETURN
    IF (
        CONTAINS (
            VALUES ( ftClassRegistration[Title] ),
            ftClassRegistration[Title], currentClass
        ),
        1,
        0
    )

Regards

[dougford99]

Very elegant solution, I'll need to remember that for future applications. Unfortunately, the visualization has multiple columns including the recommendation type (required, recommended, optional, etc.). If I display just the class title, the measure works perfectly. But if I add a second column to the visual the dataset increases. I'm happy to accept the solution since it does solve the issue if only one column of data is displayed in the visual. But I'd really appreciate any additional thoughts on how to resolve the issue when there is more than one column included in the table visual.

Thanks!

Doug