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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.