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

[jribs]

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

[parry2k]

@jribs I would surely recommend reporting it as a bug. It's a bit a strange.

[jribs · Accepted solution]

@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

[amitchandak]

@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.

[parry2k]

@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.

[BA_Pete]

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