Rules / Error Prevention

Relationship columns should be of the same data type

error RELATIONSHIP_COLUMNS_SAME_DATA_TYPE · ported · model layer · scope: Relationship

What it checks

Relationships whose two columns have different data types.

Each finding names the relationship the way Tabular Editor does, 'Sales'[Product ID] ∞←1 'Product'[Product ID], with the two cardinalities either side of an arrow that points the way the filter travels. The types themselves are not in the line, so read the two columns to see which one to change.

Example

table Sales
	column 'Product ID'
		dataType: string
		sourceColumn: ProductID

	column Amount
		dataType: decimal
		sourceColumn: Amount

table Product
	column 'Product ID'
		dataType: int64
		isKey
		sourceColumn: ProductID

	column 'Product Name'
		dataType: string
		sourceColumn: ProductName

relationship Sales_Product
	fromColumn: Sales.'Product ID'
	toColumn: Product.'Product ID'

table Sales
	column 'Product ID'
		dataType: int64
		sourceColumn: ProductID

	column Amount
		dataType: decimal
		sourceColumn: Amount

table Product
	column 'Product ID'
		dataType: int64
		isKey
		sourceColumn: ProductID

	column 'Product Name'
		dataType: string
		sourceColumn: ProductName

relationship Sales_Product
	fromColumn: Sales.'Product ID'
	toColumn: Product.'Product ID'

Why it matters

The engine relates columns by value, and when the types differ it converts one side for every query. A text key on one side and a whole number on the other works until a value like 007 meets 7, at which point rows quietly fall into the blank member. Matching types remove both the conversion cost and the surprise.

How to fix it

Set the two columns to the same type, and prefer a whole number for a key. In Power BI Desktop there are two places to do it: Column tools, Data type on the selected column, which changes the type on the model, or Transform data, where a Changed Type step in Power Query gets the type right before the data is loaded and keeps it right on every refresh. Power Query is the better of the two when the source is the reason the types differ. In the TMDL file the property is dataType on each column. Converting a text key to a whole number fails at refresh on any value that is not a number, so look for padded keys such as 007 first and strip the padding in the same Power Query step.

When to ignore it

There is nothing to ignore: where both columns name a type and the types differ, set them to the same type. The rule compares only the types the TMDL names, so a relationship with a calculated column saved with no dataType line is not reported at all (see Quirks).

To ignore this rule on one object, add annotation pbiplint.ignore = RELATIONSHIP_COLUMNS_SAME_DATA_TYPE under the object in its TMDL file. Power BI Desktop keeps the annotation. To turn the rule off for a whole project, set "RELATIONSHIP_COLUMNS_SAME_DATA_TYPE": "off" under rules in pbiplint.config.json.

Quirks

• Both sides have to resolve to a column the model declares. A relationship naming a table or a column that does not exist is skipped, so a typo in fromColumn or toColumn hides the relationship from this rule.
• The comparison is on the declared dataType, ignoring letter case.
• A relationship with a column that has no dataType line on either side, as Power BI Desktop saves most calculated columns, is not reported, since pbiplint does not know that column's type. Tabular Editor reads the type from the column's DAX, so it still reports a real mismatch on such a relationship, which pbiplint misses.
• Only the declared type is compared, never the values. Two text keys that will never match, one padded and one not, pass the rule.

Related rules

• RELATIONSHIP_COLUMNS_SHOULD_BE_OF_INTEGER_DATA_TYPE reads the same relationships from the column side and reports every column in a relationship whose type is not a whole number. Setting both sides to int64 clears both rules at once.
• FIX_REFERENTIAL_INTEGRITY_VIOLATIONS covers what a mismatch does to the data, the fact rows whose key finds nothing on the one side. pbiplint lists that rule but does not run it, because it needs statistics only a live model carries.
• MARK_PRIMARY_KEYS reports the column on the one side of the same relationship when it is not marked as the table's key.

Ported from Microsoft's Best Practice Analyzer ruleset (opens in a new tab).

Check a model for this Improve this page (opens in a new tab)