Forced to use one to many relationship b/ween date table and calculated table (w/ distinct date col)

[Anonymous]

I am forced to use one to many relationship between date table and a calculated table I created. The calculated table I created consists of Product column, return rate for the past month, column for grouping the rates, and the date one month prior to today's date. In this column, there is only one distinct date. I attempted to create a one to many relationship with this calculated table and the date table I created prior (the date table is correct and I have not had issues creating one to many relationships between the date and my fact tables). 

 

When I attempt to switch the relationship to one to many going from my calculated table to the calculated date table, a message "The cardinality you selected isn't valid for this relationship."appears. How would I go about fixing this issue?

ANOTHER ISSUE: It's not allowing me to make the relationship active either, because of ambiguity. Although, the relationship between the calculated table and material table gives me no issues. The material table is also linked to the RMA table via material ID, so I'm unsure why this issue is arising regarding the date information. 

[V-lianl-msft]

Hi @Anonymous ,

Please refer to :https://docs.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot 

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?(Please mask any sensitive data before uploading)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The problem may be with your calculated table... are you using 'ALL' or 'VALUES'? you might consider changing them to 'ALLNOBLANKROW' or 'DISTINCT'.

This article explains the difference between Values and Distinct, and you might find some more useful information on their site.

https://www.sqlbi.com/articles/blank-row-in-dax/

Hope that helps!

Kim