Relationship is not working as expected.

[olives]

I can't figure out how to get the table relationships to work.

I have a unique master of employees, stores and specialty.

employees

employee_cd	employee_name
100	Jennifer
101	Robert
102	Olive

stores

store_cd	store_name
200	StoreA
201	StoreB
202	StoreC
203	StoreD

specialty

specialty_cd	specialty
10	PC
20	Phone
30	Software

There is a list of employees and the stores they are responsible for.

repstore

store_cd	(store_name)	employee_cd	(employee_name)
200	StoreA	100	Jennifer
201	StoreB	100	Jennifer
201	StoreB	102	Olive
202	StoreC	100	Jennifer
202	StoreC	102	Olive
202	StoreC	101	Robert
203	StoreD	101	Robert

I also have employee’s specialty table 

employee_specialty

employee_cd	(employee_name)	specialty_cd	(specialty)
100	Jennifer	10	PC
100	Jennifer	20	Phone
102	Olive	30	Software
102	Olive	20	Phone
101	Robert	30	Software
101	Robert	10	PC

This relation works.

but seems this is not working.

What I want to show is the name of the store, the names of the employees, and the specialties of them.

Can anyone tell me what I need to modify?

[v-yiruan-msft · Accepted solution]

Hi @olives ,

Why the visual can't display the data normally? It is due to there is no direct relationship between the table 'specialty' and the table 'employee'&'store'...

You can create a measure as below to get the specialty, please find the details in the attachment.

Specialty Name = 
VAR _selempname =
    SELECTEDVALUE ( 'employees'[employee_name] )
VAR _selstore =
    SELECTEDVALUE ( 'stores'[store_name] )
VAR _storecd =
    CALCULATE (
        MAX ( 'stores'[store_cd] ),
        FILTER ( 'stores', 'stores'[store_name] = _selstore )
    )
VAR _rpempcd =
    CALCULATE (
        MAX ( 'repstore'[employee_cd] ),
        FILTER ( 'repstore', 'repstore'[store_cd] = _storecd )
    )
VAR _spcds =
    CALCULATETABLE (
        VALUES ( 'employee_specialty'[specialty_cd] ),
        FILTER ( 'employee_specialty', 'employee_specialty'[employee_cd] = _rpempcd )
    )
VAR _specialty =
    CONCATENATEX (
        FILTER ( 'specialty', 'specialty'[specialty_cd] IN _spcds ),
        'specialty'[specialty],
        ", "
    )
RETURN
    _specialty

Best Regards

[olives]

@v-yiruan-msft 
Thank you for your help.

This is very helpful😊 I was able to achieve what I wanted to do.