Forum Discussion
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_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?
Hi Anonymous ,
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 _specialtyBest Regards
2 Replies
Hi Anonymous ,
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 _specialtyBest Regards