Forum Discussion

LuITS's avatar
LuITS
Icon for Helper II rankHelper II
1 year ago
Solved

Relationship between Translation and Data Tables

Hello,

i have following scenario and im not able to set the right relationships.

 

Basicly i have a Report with different slicers, which first filter for a language and than a specific shift (Language filter should translate those shifts). Later i have a Datatable which need translations for specific events from another table.

 

Tables:

Language Table

Language
US
BR

Shifts Table with Shift translations

ShiftCodeLanguageShiftTranslation
S1USS1us
S1BRS1br
S2USS2us
S2BRS2br

Data Table

ShiftCodeQuantityEventCode
S1101
S2102
S3103
.........

Translation Table

EventCodeLanguageTranslation
1USus1
1BRbr1
2USus2
2BRbr2
3USus3
3BRbr2

 

As mentioned i have two slicers, first selecting the language from LanguageTable, second slicer should show translated shifts from Shifts Table.

For Example i select: LanguageSlicer: US, Shiftslicer should show early and late

 

Expected Tablevisual when selecting US and early in slicers

 

EventCode (Data Table)Translation (Translation Table)ShiftTranslation (Shifts Table)Quantity (Data Table)
1us1early10
............
Totals   

 

I can set up some relations:

Language Table   1 to many  ->  Shifts Table    with Language Collumn

Shifts Table          many to many -> Data Table    with ShiftCode Collumn

 

I dont know how to best set up relationships for Translation Table.

 

Not all rows in Data Table have a EventCode.

I need to Filter Translation Table with Language to eliminate duplicate Transaltions, but when creating relationship 1 to many betwenn Language and Translation Table (Language Collumn) i cant set up an active relation between Translation Table and Data Table with EventCode Collumn anymore (Error: you cannot create a direct relationship between Translation and Data Table, as this would lead to ambiguity between Tables Language and Translations.)

 

I just want that i can get the right Translations for Event Code in the selected Language.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,LuITS .I am glad to help you.
    Data model design and modification is a very important process, and you need to focus on the many-to-many relationships in your model
    Here is my test
    I get an error when I try to create links directly for the many-to-many relationships in your model. The system suggests that when creating a many-to-many relationship, it is guaranteed that at least one link exists on one end of the multiple ends.

    In fact, the many-to-many relationships that exist in the model are unfriendly to data processing, and you are best to avoid such relationships directly (it is better to optimize the data structure).
    For the many-to-many processing relationship, the commonly used way is to establish a bridge table between the two tables as an intermediate bridge
    I created two bridge tables, both using the Vlaues function.
    Like this:
    You need to set the filtering direction of the direct relationship between the bridge table and the original table (whether it is one-way or bidirectional) according to actual needs.
    Here is my table DAX code:

    EventCode Table = VALUES('Translation Table'[EventCode])

    ShiftCode Table = VALUES('Shifts Table'[ShiftCode])

     

    By creating a bridge table, at least the fields in the model can be connected properly and the relationship can be passed (an error will be reported if the bridge table is not used).

    Also note that because your relationship has multiple many-to-many relationships, some fields must exist in an aggregated state after they are placed in visual
    Like this:

    When some fields are not placed in visual as aggregate values, powerbi cannot properly represent and render the correspondence between the fields, resulting in an error:

    You need to modify your data model appropriately based on your actual data, add the right amount of auxiliary tables to it, and refine the model relationships.
    I hope the following power BI related documentation will be helpful to you, which describes the common official treatment of how to handle many-to-many relationships.

    URL:
    Many-to-many relationship guidance - Power BI | Microsoft Learn
    Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn
     In fact, you need to try to avoid this situation, I recommend that you try to optimize your data model structure, avoid direct many-to-many relationships, many-to-many relationships are not conducive to the presentation and analysis of data models, and are prone to some restrictive problems

    I have uploaded my original data and corresponding PBIX files, I hope it will be helpful to you.

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,LuITS .I am glad to help you.
    Data model design and modification is a very important process, and you need to focus on the many-to-many relationships in your model
    Here is my test
    I get an error when I try to create links directly for the many-to-many relationships in your model. The system suggests that when creating a many-to-many relationship, it is guaranteed that at least one link exists on one end of the multiple ends.

    In fact, the many-to-many relationships that exist in the model are unfriendly to data processing, and you are best to avoid such relationships directly (it is better to optimize the data structure).
    For the many-to-many processing relationship, the commonly used way is to establish a bridge table between the two tables as an intermediate bridge
    I created two bridge tables, both using the Vlaues function.
    Like this:
    You need to set the filtering direction of the direct relationship between the bridge table and the original table (whether it is one-way or bidirectional) according to actual needs.
    Here is my table DAX code:

    EventCode Table = VALUES('Translation Table'[EventCode])

    ShiftCode Table = VALUES('Shifts Table'[ShiftCode])

     

    By creating a bridge table, at least the fields in the model can be connected properly and the relationship can be passed (an error will be reported if the bridge table is not used).

    Also note that because your relationship has multiple many-to-many relationships, some fields must exist in an aggregated state after they are placed in visual
    Like this:

    When some fields are not placed in visual as aggregate values, powerbi cannot properly represent and render the correspondence between the fields, resulting in an error:

    You need to modify your data model appropriately based on your actual data, add the right amount of auxiliary tables to it, and refine the model relationships.
    I hope the following power BI related documentation will be helpful to you, which describes the common official treatment of how to handle many-to-many relationships.

    URL:
    Many-to-many relationship guidance - Power BI | Microsoft Learn
    Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn
     In fact, you need to try to avoid this situation, I recommend that you try to optimize your data model structure, avoid direct many-to-many relationships, many-to-many relationships are not conducive to the presentation and analysis of data models, and are prone to some restrictive problems

    I have uploaded my original data and corresponding PBIX files, I hope it will be helpful to you.

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.