Best practices to design relationships between one table and multiple tables

[Anonymous]

Dear all,

I am having the data model structure below:

And the relationships I want in order to perform cross-filtering:

1. The Overall table can both filter the Group table and the Gender table (through key "Merchant" - One-to-many relationship) with the single cross filter direction. (Overall filters Group + Overall filters Gender)
2. The Group table can filter the Gender table (through key "Group" - Many-to-many relationship) with the single cross filter direction (Group filters Gender)

I can only establish the two relationships from the Overall table. When I try to make connection between the Group and Gender table, Power BI warned me as below:

Because I cannot design the data as star-schema design, so I do not know how to implement the relationship that I mentioned before. Are there any best practices to design and set relationships so that one table can be crossed-filtering through multiple tables?

Thanks for all of your support.

[Anonymous]

After several times trying, I have another approach to handle this problem, which will be using DAX instead of creating relationships between tables.

To perform cross-filtering between multiple tables, I will check the filter status of each chart by using DAX, everyone can see this DAX as reference:

MEMBER_GENDER =

var filter_both = CALCULATE(

    SUMX(

        FILTER(Gender, Gender[Merchant] = SELECTEDVALUE(Overall[Merchant]) && Gender[Group] = SELECTEDVALUE('Group'[Group])),

        Gender[Members]

    ),

    DISTINCT(Gender[Gender])

)

var filter_merchant = CALCULATE(

    SUMX(

        FILTER(Gender, Gender[Merchant] = SELECTEDVALUE(Overall[Merchant])),

        Gender[Members]

    ),

    DISTINCT(Gender[Gender])

)

var filter_group = CALCULATE(

    SUMX(

        FILTER(Gender, Gender[Group] = SELECTEDVALUE('Group'[Group])),

        Gender[Members]

    ),

    DISTINCT(Gender[Gender])

)

var no_filter = CALCULATE(

    SUM(Gender[Members]),

    DISTINCT(Gender[Gender])

)

return SWITCH(

    TRUE(),

    ISFILTERED(Overall[Merchant]) && ISFILTERED('Group'[Group]), filter_both,

    ISFILTERED(Overall[Merchant]) && NOT(ISFILTERED('Group'[Group])), filter_merchant,

    NOT(ISFILTERED(Overall[Merchant])) && ISFILTERED('Group'[Group]), filter_group,

    no_filter

)

I do not know the effect of performance when doing this. I hope everyone can review and suggest the optimize solution. Thanks everyone.