Complex relationships

[TomPBI123]

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.

[Anonymous · Accepted solution]

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. 

[amitchandak]

@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.