Role Playing Dimensional Slicer Table

[ryan_b_fiting]

Hello Community 

I am trying to create a Role Playing table that allows me to slice my reports with a role from that table.  My data sets originally were a fact table, employee table and date table. 

My fact table has 2 columns with employee id's, one for employee assigned to the claim and one with the employee assigned to the exposure.  Because of this, I created a Claim Emp table (all employees with role 'Claims' added) and an Exp Emp Table (all employees with role 'Exp' added). 

I do not want to use USERELATIONSHIPif possible because there are a lot of metrics we are looking at. 

From the Claim Emp Table and Exp Emp Table, I have appended those table into a Role table.  It shows all employees for each Role.  What I want to do is be able to select the role from this table and use it as a slicer to be able to slice the data by the different roles.  Here is sample data:

FACT TABLE

ID	Count	ExpID	ClmID	ROWwid
1	45	123	123	10
2	56	129	139	11
3	234	155	158	12
4	576	123	183	13
5	23	125	155	15

CLAIM EMP TABLE

Row ID	Emp Name	Role	Zone
123	A	Claim	North
139	B	Claim	South
158	C	Claim	South
183	D	Claim	West
166	E	Claim	East
129	F	Claim	East
155	G	Claim	West
125	H	Claim	Noth

EXP EMP TABLE

Row ID	Emp Name	Role	Zone
123	A	Exp	North
129	F	Exp	East
155	G	Exp	East
125	H	Exp	Noth
139	B	Exp	South
158	C	Exp	South
183	D	Exp	West
166	E	Exp	East

ROLE TABLE

Row ID	Emp Name	Role	Zone
123	A	Exp	North
129	F	Exp	East
155	G	Exp	East
125	H	Exp	Noth
139	B	Exp	South
158	C	Exp	South
183	D	Exp	West
166	E	Exp	East
123	A	Claim	North
139	B	Claim	South
158	C	Claim	South
183	D	Claim	West
166	E	Claim	East
129	F	Claim	East
155	G	Claim	West
125	H	Claim	Noth

Is there anyway to model this data so that I can select the role, and it will filter the fact data based on the emp id role columns from the fact table?(ExpID and ClmID)

I am not sure that this is possible, but wanted to reach out to the community.

Thanks

Ryan

[amitchandak · Accepted solution]

@ryan_b_fiting , refer if this can help

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[ryan_b_fiting]

This does not really solve the issue, but I believe it confirms what I thought and that the full solution my client is looking for is not actually possible.

Thank you for the information.