The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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) 😔
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.
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.
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?
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.
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!!