Forum Discussion
Complex relationships
Hello, I am having trouble with filtering across complex relationships. I have the following:
TABLE 1 (Firm):
- SYSID
TABLE 2 (Agreement)
- FIRM_SYSID
- ID
TABLE 3 (Cateory)
- ID
- VARIABLE
TABLE 1 and TABLE 2 have a 1-many relationship and join on SYSID - > FIRM_SYSID
TABLE 2 and TABLE 3 have a 1-many relationship and join on ID - > ID
There are no unique identifiers in TABLE 3, because an agreement can belong to multiple categories, and a category can have multiple agreements.
I would like to filter for a given variable (category), which tells me how many IDs (agreements) are in that variable, and so therefore also tells me how many SYSIDs (firms) are affected by that variable. E.g. a variable X might have 50 different IDs, which live in 6 different SYSIDs. So I want to filter from variable X to know there are 6 SYSIDs. However, within those 6 SYSIDs, there may be many more than 50 IDs.
My current problem is that I cannot structure the relationships in such a way that I can filter a variable X and understand how many SYSIDs that affects :(. Any help here is greately appreciated, I have been trying numerous options over several days to no success.
Hi TomPBI123
I build a sample to have a test.
FIRM:
Agreement:
Category:
Relationship:
You want distinct count of SYSID by filter. So if I select X, I should get 2 (SYSID should be 1,1,1,2,distinct count should be 2) as a result.
First way, try this measure.
Measure = CALCULATE(Distinctcount(Agreement[FIRM_SYSID]),FILTER(ALL(Agreement),Agreement[ ID] IN VALUES(Cateory[ID])))Second way, you can change the relationship direction (between Category and Agreement) from single to both.
Then build a card visual add FIRM_SYSID column into it and use distinct count function.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
TomPBI123 , based on what I got. Try to make join between table 2 and table three as bi-directional
Try a measure like Distinctcount(Table2[FIRM_SYSID])
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi TomPBI123
I build a sample to have a test.
FIRM:
Agreement:
Category:
Relationship:
You want distinct count of SYSID by filter. So if I select X, I should get 2 (SYSID should be 1,1,1,2,distinct count should be 2) as a result.
First way, try this measure.
Measure = CALCULATE(Distinctcount(Agreement[FIRM_SYSID]),FILTER(ALL(Agreement),Agreement[ ID] IN VALUES(Cateory[ID])))Second way, you can change the relationship direction (between Category and Agreement) from single to both.
Then build a card visual add FIRM_SYSID column into it and use distinct count function.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
