Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Best practices to design relationships between one table and multiple tables

Dear all,

I am having the data model structure below:

NghiaPLT_0-1676011448882.png

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:

NghiaPLT_1-1676012003360.png

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.

1 REPLY 1
Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.