Help with linking related cases table to main case table

[awg201]

Hello 😊

I'm stuck with a specific requirement to be able to show the related cases when a case is filtered. There are two tables:

tblCase - which stores all unique Case IDs:

CaseID	Name	Gender
A	Alfred	Male
B	Ben	Male
C	Charlie	Male
D	Danielle	Female
E	Eaton	Male
F	Fred	Male
G	Greg	Male

tblCaseLink - which has a Link ID 

CaseID	LinkID
A	1
B	1
C	1
D	2
E	2
F	3
G	3

A relationship by Case ID would be one to one, but the problem is when I filter for CaseID "A", it will only show me that case, instead of the Case "B" and "C", which are related by the LinkID.

I tried to merge the LinkID to tblCase, but when I try to get details from the related cases in would just repeat the values from the filtered case. For exmaple, if i filtered for case "A", and I want to get the Name column, case "B" and "C" will just show "Alfred" from case "A" instead of the correct related case names.

Ideally, the below would be the desired result if it were a table visual:

> Applied slicer: Case ID = "A" 

Title: Related Cases
Case ID	Name	Gender
B	Ben	Male
C	Charlie	Male

Would anyone be able to share their knwoeldge on this (so I can get some sleep) 😔

[v-kathullac]

Hi @awg201  ,

we wanted to kindly follow up to check if the solution provided by the superuser resolved your issue? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @awg201  ,

we wanted to kindly follow up to check if the solution provided by the superuser resolved your issue? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[awg201]

Thanks Ashish!

I have studied and applied your logic from your file. I never thought it would be this complex! (for me atleast).

The measures work for showing attributes from the case link table, but in my situation, the case link table only has two columns - CaseID and LinkID. All my attribute values are stored in the case table, with other dimension tables connected to it. How would I be able to access those values to display?

[Ashish_Mathur]

You are welcome.  Cannot understand what you are trying to say.  When you apply my solutin to your data, clearly show the problem that you are facing.

[awg201]

Thanks Ashish,

In your example, the details of the case is stored in the link table, which can be retrieved with measures as you have.

But in my situation, the details are from tblCase, with many dimension tables connected to it. When I add these details such as age, location etc, it is just repeating values from the sliced case ID.

Essentially, if I slice case ID "A" from in tblCase I should see the following related results:

CaseID	Name	Gender	Age
A	Alfred	Male	23
B	Ben	Male	12
C	Charlie	Male	55

I've attached a very small example of a dummy dataset and the same logic applied from your attached solution: linked_cases_help.pbix

Hopefully it makes sense...appreciate your assistance!!

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.