cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hedborg Frequent Visitor
Frequent Visitor

Maximum nr of unique values

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

1 ACCEPTED SOLUTION

Accepted Solutions
KGrice Established Member
Established Member

Re: Maximum nr of unique values

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:

 

CountRows.PNG

 

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.

View solution in original post

4 REPLIES 4
KGrice Established Member
Established Member

Re: Maximum nr of unique values

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 Frequent Visitor
Frequent Visitor

Re: Maximum nr of unique values

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 Established Member
Established Member

Re: Maximum nr of unique values

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:

 

CountRows.PNG

 

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.

View solution in original post

hedborg Frequent Visitor
Frequent Visitor

Re: Maximum nr of unique values

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 868 guests
Please welcome our newest community members: