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

Relationship is not working as expected.

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_cdemployee_name
100Jennifer
101Robert
102Olive

stores

store_cdstore_name
200StoreA
201StoreB
202StoreC
203StoreD

specialty

specialty_cdspecialty
10PC
20Phone
30Software

 

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

repstore

store_cd(store_name)employee_cd(employee_name)
200StoreA100Jennifer
201StoreB100Jennifer
201StoreB102Olive
202StoreC100Jennifer
202StoreC102Olive
202StoreC101Robert
203StoreD101Robert

 

I also have employee’s specialty table 

employee_specialty

employee_cd(employee_name)specialty_cd(specialty)
100Jennifer10PC
100Jennifer20Phone
102Olive30Software
102Olive20Phone
101Robert30Software
101Robert10PC

 

This relation works.

olives_0-1674617461620.png

but seems this is not working.

olives_3-1674617597984.png

 

olives_4-1674617718819.png

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?

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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'...

yingyinr_1-1674713773027.png

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

yingyinr_0-1674713687082.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
yingyinr
Community Support
Community Support

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'...

yingyinr_1-1674713773027.png

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

yingyinr_0-1674713687082.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@yingyinr 
Thank you for your help.

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.