Forum Discussion

jribs's avatar
jribs
Helper I
5 years ago
Solved

Invalid Relationship when attempting Many-To-One despite Unique Values

Hey there,

 

When attempting to create a relationship between two tables, Power BI is simply saying that selecting a "One to Many" relationship for a lookup-to-fact table is invalid. Both are from a SAP BW data source. Somewhere on the internet, it said to try and "Remove Errors" from both Key columns. This did not work either. Any suggestions?

 

The error where lookup table = "ABC Indicator" and fact table = "Maintenance Items":

 

The lookup table (ABC Indicator) only has 4 rows, so no double values here:

 

Thank you in advance

Need Help
  • jribs's avatar
    jribs
    5 years ago

    amitchandak parry2k 

     

    Thank you both for reaching out. 

     

    Changing the cross-filter did not work. However, your feedback on it being strange it was an option helped out. SAP Business Warehouse uses "#" as a blank value key (with the intention, I assume, of not using blank values). After replacing all "#" values with "NA" in both [ABC Indicator].Key and [Maintenance Item].ABC_Key, now the relationship shows a One to Many relationship as the default without error.

     

    Before closing this out, where might I be able to report this to Microsoft, assuming this is a bug where Power BI does not escape "#" values?

     

    Thank you all for the help

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jribs , I doubt the key is repeating in ABC Indicator or has a blank/null value.

    Second thing is that you need to have a single direction relation from ABC Indicator to Maintenance Items. But if your keys are correct, it will not show the other option.

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi jribs ,

     

    It looks like you're trying to set up your relationship whereby your Many side filters your One side.

    Try changing 'Cross filter direction' to 'Single (ABC Indicator filters Maintenance Items)'.

     

    Pete

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    jribs Bit confused, how come an option showed up in cross filter direction when there is "one to many" relationship. This is super weird. I cannot even reproduce it. This option only available when there are "many to many" relationships.

  • jribs's avatar
    jribs
    Helper I
    5 years ago

    amitchandak parry2k 

     

    Thank you both for reaching out. 

     

    Changing the cross-filter did not work. However, your feedback on it being strange it was an option helped out. SAP Business Warehouse uses "#" as a blank value key (with the intention, I assume, of not using blank values). After replacing all "#" values with "NA" in both [ABC Indicator].Key and [Maintenance Item].ABC_Key, now the relationship shows a One to Many relationship as the default without error.

     

    Before closing this out, where might I be able to report this to Microsoft, assuming this is a bug where Power BI does not escape "#" values?

     

    Thank you all for the help