Why does the ALLSELECTED function return a new blank row

Hi again Vishesh mail2vjj,

Here is a bit more detail on my knowledge of this feature of the Power BI engine:

Let's say there is a 1:many relationship between OneTable and ManyTable.

Regardless of the cross filter direction, the table on the 1-side of any relationship (OneTable) will have a notional blank row added to handle missing values in the relationship if there is at least one missing value (i.e. if there is at least one value in ManyTable but not in OneTable in the related columns).

This row is never visible in the Data view, and will not exist in a direct reference to the table. But the blank row is visible when OneTable is passed to functions such as ALL, ALLSELECTED, & VALUES.

Intuitively, the reason for this is so that the engine can relate a row of OneTable to every row of the ManyTable.

This is useful because in any situation where a column from the 1-side table is visible (e.g. a column of a table visual or an axis of a chart), a blank will appear corresponding to unmatched rows in the many-side table. So those values won't be "lost".

Internally, when two tables are related, the engine creates an "expanded" version of the ManyTable, by carrying out a left outer join between ManyTable and OneTable, and the blank row corresponds to the "empty" row of the OneTable that is joined to ManyTable.

It is useful to know when the blank row is included or excluded.

Off the top of my head, these functions will include the blank row:

• ALL( OneTable ) or ALL( OneTable[column] )
• ALLSELECTED( OneTable ) or ALLSELECTED( OneTable[column] ) (as long as blank hasn't been filtered out)
• VALUES( OneTable ) or VALUES( OneTable[column] ) (as long as blank hasn't been filtered out)

These functions will exclude the blank row:

• DISTINCT( OneTable ) or DISTINCT( OneTable[column] )
• ALLNOBLANKROW( OneTable )

Hope that helps,

Owen :)