Forum Discussion

Hussein_charif's avatar
Hussein_charif
Helper V
10 months ago
Solved

Relationship Issue

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 f...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Irwan's avatar
    Irwan
    10 months ago

    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.

rohit1991's avatar
rohit1991
Super User
10 months ago

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's avatar
Hussein_charif
Helper V
10 months ago

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

  • Irwan's avatar
    Irwan
    Super User
    10 months ago

    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.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    10 months ago

    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