Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hedborg
Regular 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

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

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

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

 

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.