No more unique values in calculated table as of July 2021?

Hi,
I have a data model that contains a calculated table that creates a key column using DISTINCT and VALUES. The created column actually contains unique values and I could build a 1:1 relationship with this column. This worked without problem until mid of July 2021. Then suddenly the scheduled refresh failed. I looked at the file and Power BI did not accept a “one” relationship with this column anymore, only a “many” relationship - although the actual values are unique, of course, by definition of the DISTINCT function.
Did anyone of you experience the same and have a solution? A m:n relationship doesn’t sound like a generally acceptable to me. What can be the cause if Power BI does not accept a “one” relationship even though the column contains only unique values?
If there is no other solution my next approach would be to create the key column in Power Query so it’s not a calculated tabel anymore.
BR
Martin

@Martin ,

Without seeing your data and data model, I can’t tell specifically why the refresh is failing, but I do know that in Power BI one-to-one relationships can cause problems, particularly with regard to blanks. As a matter of course, whenever Power BI designates a relationship from a dimension to a fact table as 1:1, I immediately change that to 1:M, even if I know all the values on the M side will actually be distinct. 1:1 is just a subset of 1:M, so this works fine.

– Brian

2 Likes

Hi @Martin ,

You can verify your data source very well, it might contain a blank row and this can affect the relationship that you are working with.

To mitigate this error, you can go to your query editor and ensure you checklist that remove blank in that table that has the distinct values

2 Likes

Hi @Martin, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

1 Like

Seems like using DISTINCT instead of VALUES solved the issues. Not really sure why, but maybe a blank was introduced and not allowed as a key.
BR
Martin

@Martin If you want to know more about DISTINCT vs VALUES and the Blank Row, I’d recommend checking out this playlist in the same order. One more video is to be made on performance difference between DISTINCT and VALUES, right now I am looking for some good examples.

1 Like

Thank you very much @AntrikshSharma