topic Re: Remove blank row from calculated table based on filter in DAX Commands and Tips

Remove blank row from calculated table based on filter

Anonymous — Tue, 04 Apr 2023 22:10:55 GMT

I have a table of key-value pairs named Entity and Role.

Entities are companies participating in a transaction.

Roles fall into two groups, buyers and sellers.

For buyers, the entity field always contains the string 'buyer'.

I'm able to create a dimension table for buyers as follows:

dim_buyer =
CALCULATETABLE (
   VALUES( Data[Entity]),
   FILTER( Data, 
      CONTAINSSTRING( Data[Role], "buyer")
   )
)

The resulting table includes a blank row that I want to eliminate.

In addition, I want to create a companion table that includes sellers. This is defined as distinct values of Data[Entity] where Data[Role] does NOT contain the string "buyer", so that's easy enough:

dim_Seller = 
CALCULATETABLE (
   VALUES(Data[Entity]),
   FILTER( Data,
      NOT(CONTAINSSTRING( Data[Role], "buyer"))
  )
)

I also do not want this table to contain a blank row.

I've tried to filter the result of the CALCULATETABLE to eliminate the blank rows, but I don't have a column name. I tried ADDCOLUMNS in place of VALUES, but then I get the whole table.

Any help is very much appreciated.

Re: Remove blank row from calculated table based on filter

FreemanZ — Tue, 04 Apr 2023 22:22:03 GMT

hi Anonymous

try like:

dim_buyer =
CALCULATETABLE (
   DISTINCT( Data[Entity]),
   FILTER( Data, 
      CONTAINSSTRING( Data[Role], "buyer")
   )
)

Re: Remove blank row from calculated table based on filter

Anonymous — Tue, 04 Apr 2023 22:39:09 GMT

Thanks FreemanZ

This works for the dim_buyer table, but I still get a blank row in the dim_seller table.
I double-checked the field values, and there are none with a space or other non-printable character. I also retried replacing blanks with null in Power Query Editor but the blank row remains.

Re: Remove blank row from calculated table based on filter

FreemanZ — Tue, 04 Apr 2023 22:43:06 GMT

how about replacing VALUES with DISTINCT as well, like:

dim_Seller = 
CALCULATETABLE (
   DISTINCT(Data[Entity]),
   FILTER( Data,
      NOT(CONTAINSSTRING( Data[Role], "buyer"))
  )
)

Re: Remove blank row from calculated table based on filter

Anonymous — Tue, 04 Apr 2023 22:53:13 GMT

That's what I did, but I was still getting a blank row.

I think the problem is due to the fact that there are blank or null rows in the table, so if I say "does not contain the string 'buyer'", it will match.

After thinking about the data a bit more, the rows with no buyer or seller are not meaninful. I filtered those out, and now there is no more blank row in the seller table.

Thanks very much for your help. I will mark your response as a solution.

Re: Remove blank row from calculated table based on filter

FreemanZ — Tue, 04 Apr 2023 23:00:21 GMT

Another occasion for unexpected blanks:

if a table is

1) on the one side of a relationship and

2) does not have all the values in the related colunn from the many side

a blank value will be added automatically.