Multiple values in distinct table

[schaema3]

Hi, when using the 'Distinct' function to create a new table it should return an unique value based on the column selected. When trying to create a relationship from this new unique value table I am getting the following error: "You can't create a relationship betwenn these two columns because one of the columns must have unique values." I was under the impression creating a 'Distinct' table the values returned must be unique. Also removing blank cells doesn't work if it's a table and not a querry. Can anyone suggest what I am doing wrong?

[schaema3 · Accepted solution]

I think I found a workaround by using the following DAX: PivotTable = DISTINCT(FILTER(VALUES(SourceFile[SourceColumn]),LEN(SourceFile[SourceColumn])>0)). That way I removed any blank lines that actually caused the Error with 'Multiple' values. As a result, Power BI reads a blank column not as a value. Maybe there's an easier way but in my case it resolved the problem.

[v-yuezhe-msft]

@schaema3,

Could you please post your DAX and share sample data of your tables? To solve the above error message, please check my DAX in the similar thread below.

https://community.powerbi.com/t5/Service/Cannot-create-relationship-between-two-columns/td-p/121783

Regards,
Lydia

[schaema3 · Accepted solution]

I think I found a workaround by using the following DAX: PivotTable = DISTINCT(FILTER(VALUES(SourceFile[SourceColumn]),LEN(SourceFile[SourceColumn])>0)). That way I removed any blank lines that actually caused the Error with 'Multiple' values. As a result, Power BI reads a blank column not as a value. Maybe there's an easier way but in my case it resolved the problem.

[v-yuezhe-msft · Accepted solution]

@schaema3,

Glad to hear the issue is solved, you can accept appropriate replies as answer to close this thread.

Regards,
Lydia