Need to return a list from 2 tables that are related

[bradytb]

I have two tables:

Classes with CIP- Name of Class, CIP code, Vcode

VCodeList_all- Teacher name, Vcode

The Vcodes repeat in both tables.  Teachers can have several Vcodes on their teaching cert.  Classes also have several Vcodes that work for teaching.  

Classes with CIP:

Name of Class	CIP code	Vcode
Plant Science	010601	V010601
Plant Science	010601	V010000
Urban Farming	011201	V010000
Yearbook	091001	V091001

VCodeList_all

Vcode	Staff Name
V091001	Elvis Presley
V010601	Nancy Sinatra
V010000	Nancy Sinatra
V010000	Big Bopper

Note that the Vcodes repeat for some classes.  The idea is to have a list of teachers return when you put in a class name or list of classes when you put in a teachers name.  

EX:

If I type in V010000 it should return the class name that this Vcode works for 

Plant Science

Urban Farming

And the teachers that can teach it are:

Nancy Sinatra

Big Bopper

Is this even possible? 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Staff measure: = 
CONCATENATEX (
    SUMMARIZE (
        CALCULATETABLE ( Staff_V_fct, SUMMARIZE ( Class_V_fct, V_dim[Vcode] ) ),
        Staff_dim[Staff Name]
    ),
    Staff_dim[Staff Name],
    ", "
)

Class measure: = 
CONCATENATEX (
    SUMMARIZE (
        CALCULATETABLE ( Class_V_fct, SUMMARIZE ( Staff_V_fct, V_dim[Vcode] ) ),
        Class_dim[Name of Class]
    ),
    Class_dim[Name of Class],
    ", "
)

[ThxAlot · Accepted solution]

A bridge table is enough,

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Staff measure: = 
CONCATENATEX (
    SUMMARIZE (
        CALCULATETABLE ( Staff_V_fct, SUMMARIZE ( Class_V_fct, V_dim[Vcode] ) ),
        Staff_dim[Staff Name]
    ),
    Staff_dim[Staff Name],
    ", "
)

Class measure: = 
CONCATENATEX (
    SUMMARIZE (
        CALCULATETABLE ( Class_V_fct, SUMMARIZE ( Staff_V_fct, V_dim[Vcode] ) ),
        Class_dim[Name of Class]
    ),
    Class_dim[Name of Class],
    ", "
)