Forum Discussion

TomPBI123's avatar
TomPBI123
New Member
5 years ago
Solved

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 - V...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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.