Why does the ALLSELECTED function return a new blank row

[mail2vjj]

This should be very easy to figure out but for some reason I am unable to.

I have this 'Incentive' table in PowerBI and I tried to create a duplicate of this table using the ALLSELECTED function, but it gives me an additional blank row.

I know I can create the table in the query by using duplicate table, but I wanted to know the behavior of the ALLSELECTED function.

Here are the pictures:

Incentive TableThe extra row in the new table with 'AllSelected' Function

I know this might sound like a very silly question but as I said above I am just trying to understand the behavior of the function.

Thanks,

Vishesh

[OwenAuger]

Hi Vishesh @mail2vjj

This will happen if there is a relationship between the Incentive table and another table, with values on the many-side that are unmatched on the one-side (in the Incentive table). Presumably you have related Incentive to another table?

Basically, a blank row is added to the Incentive table behind the scenes corresponding to these unmatched values in the related table. The blank row is visible in ALL(Incentive), ALLSELECTED(Incentive) or VALUES(Incentive).

The blank row will not be visible in ALLNOBLANKROW(Incentive) or DISTINCT(Incentive).

Regards,

Owen

[mail2vjj]

Hi Owen @OwenAuger,

Thank you so much for your reply.

Yes, you correctly figured out that there is a relationship between the Incentive table and another table. As soon as I deleted that relationship, the blank row in the new table was also removed.

So your solution worked, but then it raised another question.

The relationship that I set up was a One(Incentive) to Many (Sales. I have not posted the picture of the Sales table.) relationship and it was NOT Bidirectional. The Cross Filter direction is set to Single only.

So even after that how did the data from the Sales table(Many Side) make it to the Incentive table(One Side).

Again, thank you for your previous response.

Vishesh Jain

[OwenAuger · Accepted solution]

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 🙂

[mail2vjj]

Hi Owen @OwenAuger,

It all makes sense now.

Thank you for taking the time to type that detailed explanation. 

[GilbertQ]

Hi @mail2vjj

If you want a duplicate of the table why not just use the following syntax below.

Table = 'Incentive'

That would create an exact duplicate?

[mail2vjj]

Hi @GilbertQ,

I know what you suggested will work and there are so many other ways to create a duplicate table, but I was just trying to experiment with the formulas.

As I mentioned in my question as well, I am trying to understand the behavior of the ALLSELECTED function for when I use it in the future.

However, thank you so much for your input and taking the time to respond.

Thanks,

Vishesh Jain