cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dougford99 Frequent Visitor
Frequent Visitor

Simple table visualization using data from four related tables

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 TitleRecommendation
NetworkingRecommended
SecurityRequired
VirtualizationRecommended
Windows Server ManagementOptional

 

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

Model.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Simple table visualization using data from four related tables

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. Smiley Happy

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

 

Regards

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Simple table visualization using data from four related tables

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. Smiley Happy

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

 

Regards

dougford99 Frequent Visitor
Frequent Visitor

Re: Simple table visualization using data from four related tables

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 231 members 2,329 guests
Please welcome our newest community members: