Maximum nr of unique values

[hedborg]

Hi,

Trying to create a relationship between a big Fact table and a big Product table.

Get an error message about "one of the columns have to have unique values".

My Product table has unique Product Ids, so I cannot see what is the problem. E.g. COUNT(Product Id) = COUNTDISTINCT(Product Id).

When I try just a selection of a few Products Ids it works.

What could I have missed? Is there a maximum of Product Ids allowed. 

My table has 2.5 million products.

Kind regards

Niklas

[KGrice]

I assume you're joining by the ProductID column, the column with unique values in the Product table? Probably a silly question, but I just created two tables with 3 million rows each, each one with an ID column, and could create the relationship between the two without issue. Unless it's possible that there is a restriction on the relationship based on the type of data source...

[hedborg]

Yep, or in Power BI terms: "Create a relationship between Product > Product Id and Fact > Product Id".

Troubleshooting a bit longer I seem to have "solved" the problem by creating a pure one column table with Product Id.

Then putting that in between Fact and Product.

My new one column table had 8 less rows than my Product table, so I guess there was some strange null, blank, line break or similar hiding in there.

Difficult to find in a 2.5 million haystack.

So most likely not a maximum, but a very strict "unque" concept.

Niklas

[KGrice · Accepted solution]

If you're trying to find those off values, you could try cleaning up a few things in Edit Queries and see if that clears them. Click the column filter and Remove Empty to get rid of nulls. Remove Rows > Remove Duplicates to get rid of duplicates. If it's a number, you could try making sure the column is a number data type if it isn't already, and remove any errors after converting it.

I also realized its' possible to have duplicates where COUNT[ProducID] is equal to DISTINCTCOUNT[ProductID]. An example:

My dataset had 12 rows, containing the numbers 1-10, a duplicate value of 1, and a blank. COUNT only counts the numbers, and leaves out the blank value, resulting in 11. DISTINCTCOUNT picks up the blank row, and only counts 1 once, also resulting in 11. Comparing against COUNTROWS would probably be a better indicator, since it will pick up both and result in 12.

[hedborg]

Thanks a lot for your valuable input.

I actually tried both the Remove Empty and the Remove Duplicates, but that did not work.

The different handling of DISCOUNTCOUNT and COUNT as interesting.

I come from a Qlik background, but Power BI really seems promising.

Kind regards

Niklas