Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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:
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |