Creating Relationship Question

[leo3690]

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

[vanessafvg]

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)

[leo3690]

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

[vanessafvg · Accepted solution]

Great, will you accept this as a solution?