March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
This seems to be super simple but for some reason I am getting an error message that says I can't create a relationship between these two columns becasue one of the columns mush have a unique values. I am trying to use PEN ID as the join criteria. Any help would be appreciated.
Here is what I have in my two tables.
Pen Table:
Pen ID | PEN |
5001 | 1 |
5002 | 2 |
5003 | 3 |
5004 | 4 |
5005 | 5 |
5006 | 6 |
5007 | 7 |
5008 | 8 |
5009 | 9 |
5010 | 10 |
5011 | 12 |
5012 | 14 |
5013 | 16 |
5014 | 18 |
5015 | 20 |
5016 | 21 |
5017 | 23 |
5018 | 24 |
5019 | 99 |
FFAPOC Table: There are other columns but they are not relevant to the relationship.
DATE | Pen ID | PEN |
09/26/2016 | 5016 | 21 |
09/26/2016 | 5017 | 23 |
09/27/2016 | 5016 | 21 |
09/27/2016 | 5017 | 23 |
09/28/2016 | 5016 | 21 |
09/28/2016 | 5017 | 23 |
09/29/2016 | 5019 | 99 |
09/29/2016 | 5016 | 21 |
09/29/2016 | 5017 | 23 |
09/30/2016 | 5019 | 99 |
Solved! Go to Solution.
Great, will you accept this as a solution?
Proud to be a Super User!
one of the tables in the relationship must have unique values, i.e FFAPOC should be the many side and the pen should be on the one side i am assuming? you therefore need to clear out the duplicates of pen.
the best way to check if you have duplicates is to create 2 measures on the table
no of rows = countrows(pen)
count unique values = distinctcount('pen'[pen])
if those 2 numbers are the same then you good if you have more rows than unique values you have duplication going on
stick them on a matrix and sort by nowofrows to find offending pen
also if you have any blank fields in pen that will also create an issue, pad those values with a -1 (something that has no business value)
Proud to be a Super User!
@vanessafvg Thank you so much. Looks like PowerBi was seeing the blanks as nulls and counting those. This little trick was awesome and was able to create the relationships.
Great, will you accept this as a solution?
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |