Relationship Issue

[Hussein_charif]

Hello,

im having a problem with a report that was created beforehand, the main issue is in the following two tables we have :

Journals, Structure.

Journals is the fact table we're getting from F&O, it has a column called StructureID, which was created by the following concatenation:
isProject, MainAccount,Cost Center.

isProject has values Yes if the Mainaccount has a project, and Blank if it doesnt.

MainAccount is the Account Number.

Cost Center dislplays the cost center names that the main account has, could be multiple for 1 mainaccount.

Structure is the Structure table that contains the ID column(same as the journal table), the category and subcategory for each ID.

here are a few examples of how the ID could look like:

601101BESWA EMS (maintaccount + cost center)

999978NEGYes (mainaccount + cost center + has project)

675002 (mainaccount without project or cost center, this indicates that the category and subcategory should display everything for this account)

the relationship between the structure and journal tables is many to many and filter direction set to both, which already brings out the problem.

in my case, all categories and subcategories that have a mainaccount with a cost center or a project, show the amount correctly. the category and subcategory that have only the mainaccount, without project or cost center, show 0 amount, until i make the relationship filter direction only 1 direction (Journal filters structure) it shows the correct amounts for the categories and subcat. with only the mainaccount as an ID.

here are some pictures:
this 1 shows the amounts, where i have set the filter direction as single, with the journal table filtering the structure tablethis is the main table when i set the filter direction as single like the one above, no amounts are shown.this one is with filter direction set to both, it shows the correct total amount somehow, but the amount on the IDs is set to 0.this is the main table to show, with filter direction set to both, showing all the correct amounts except for the structures that have only the mainaccount, as you can see the amount for the Difference in exchange for jan and feb

can anyone help me identify the issue and how i can fix it? i tried creating a bridge table with unique IDs, but it didnt help, that's all i tried. i know the structuring of the tables is wrong and inefficient, but this is what i was given to work with and had to continue with. 

thank you in advance.

[v-nmadadi-msft]

Hi @Hussein_charif 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[HarishKM]

@Hussein_charif Hey,
I will follow below steps to troubleshoot.

Many-to-many relationship between "Journals" and "Structure" is complex and can lead to ambiguous filtering. Consider if a one-to-many relationship is possible, restructuring as needed.
Set up filter directions carefully. Single direction (Journal filters Structure) can resolve ambiguity in cases where IDs show 0 amounts. It ensures Journals are the context within which Structure is evaluated.
Ensure the bridge table is correctly configured. It should contain unique IDs linking Journals and Structure properly. Validate its effectiveness in managing filter propagation.
Examine the logic creating the StructureID concatenation. Consider adding columns like Project Flag separately in Structure for filtering ease.
Calculated Columns/Measures:

- Create calculated columns or measures to handle specific scenarios instead of relying solely on relationships.
- For cases with only MainAccount, devise measures to aggregate/assign amounts differently.

- Use Power BI’s “Performance Analyzer” to trace incorrect computation paths.
- Temporarily remove relationships, verify expected outputs via DAX, and adapt based on findings.

- Use report table configurations to ensure visibility with proper amounts on IDs with only MainAccount. Consider conditional logic in DAX to handle nulls or blanks delicately.

Thanks

Harish M

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

[v-nmadadi-msft]

Hi @Hussein_charif 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[rohit1991]

Hi @Hussein_charif 

The problem comes from the many-to-many relationship between your Journal and Structure tables. Both contain repeating IDs, so Power BI cannot aggregate correctly when both tables are filtered.

Steps to Fix :

1. Create a Bridge Table
   • From Structure, create a distinct list of all unique ID values (covering both Cost Center and Project).
   • You can do this with DAX:
   • Bridge_IDs = DISTINCT ( UNION ( SELECTCOLUMNS(Structure, "ID", Structure[ID]) ) )
2. Update Relationships
   • Connect:
     Bridge_IDs[ID] >> Journal[ID]
     Bridge_IDs[ID] >> Structure[ID]
   • Both should be one-to-many (single direction) from Bridge >> other tables.
3. Use Bridge for Filtering
   • Place visuals based on Structure fields (Category, SubCategory, etc.).
   • Measures will now evaluate correctly through the Bridge, avoiding circular filtering.

[Hussein_charif]

Hello, i have tried this, i couldnt get the amounts to show for the categories and subcategories, i was getting an error that there is no relationship between the fields (i am using fields from the structure table[category and subcategory] and the amount from the journal table), even if i put the ID under the categories and subcategories in my matrix. the only way it showed the amounts was when i put the ID at the first level of the matrix, but even then if i go down the levels to the categories and subcategories i get 0 amounts

[v-nmadadi-msft]

Hi @Hussein_charif ,
Thanks for reaching out to the Microsoft fabric community forum.

You have mentioned that you have tried to create a bridge table, but to fix the issue you are experiencing we have to correctly create and configure the bridge table then only it will resolve the ambiguity issue.
create a proper bridge table for the StructureID. To do this, build a distinct table containing all unique StructureID values from both Journals and Structure

Then, create two one-to-many relationships: Bridge_Structure[StructureID] to Journals[StructureID] and Bridge_Structure[StructureID]  to Structure[StructureID]. Set both relationships to single-directional (from the bridge to each table), and update visuals to use fields from Structure and measures from Journals. This typically resolves filter ambiguity and ensures all categories and subcategories display amounts correctly, including those linked only to the MainAccount.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[Irwan · Accepted solution]

hello @Hussein_charif 

as mentioned by @rohit1991 , the problem with many-to-many is power bi can not tell which to which when you have repeating id.

sharing my experience for many-to-many relationship in my previous project, i created a calculated column in both table for "dummy" unique id which come from concatenate two column.

this way is better than having relationship only from one column which has repeating id.

Hope this will help.

Thank you.