Add a row with custom value in Report Builder

[GeorgeColl]

I have the following code currently pulling through a list of names, these go in as a parameter option in my report.

EVALUATE SUMMARIZECOLUMNS('CAClaims'[Insured Name Simplification])
ORDER BY 'CAClaims'[Insured Name Simplification]

I need to add an option such as "  _No value exists" so that nothing is pulled through. I've seen that UNION would work, but I'm unsure how to create a table with a single row and specific value.

For clarification:

Currently I have the following, where all values come from my data source:

Insured Name Simplification

A

B

C

I would like to have the following, where "  _No value exists" is added manually in the query:

Insured Name Simplification

_No Value Exists

A

B

C

[Akash_Varuna · Accepted solution]

Hi @GeorgeColl You can use UNION to add a custom row manually. Something like this:

EVALUATE
UNION(DISTINCT('CAClaims'[Insured Name Simplification]), ROW("Insured Name Simplification", "_No Value Exists"))
ORDER BY 'Insured Name Simplification'

This combines the distinct values with a manually created row _No Value Exists, then sorts the result.

[DataNinja777 · Accepted solution]

Hi @GeorgeColl ,

To add a custom row like " _No Value Exists" to your list of values in DAX, you can use the UNION function to combine your original dataset with a manually created one-row table. This is useful when you want to include a default or placeholder option in your parameter list in Report Builder.

The key to achieving this is the ROW function, which lets you define a table with a single row and one or more columns. In your case, you only need one column — Insured Name Simplification — and one value for that column.

Here's how you can modify your existing query:

EVALUATE
UNION(
    ROW("Insured Name Simplification", "  _No Value Exists"),
    SUMMARIZECOLUMNS('CAClaims'[Insured Name Simplification])
)
ORDER BY [Insured Name Simplification]

This query will return a table with your original insured names from the CAClaims table, along with one additional row that has the custom value " _No Value Exists". The ORDER BY ensures that the list appears sorted, and your custom entry will be positioned accordingly, depending on its alphabetical order.

If you plan to make this value behave differently — for example, not applying a filter when selected — you'll need to account for it in your measures or filtering logic. But for the purpose of simply adding it to the dropdown list, this query does exactly what you need.

Best regards,

[Akash_Varuna · Accepted solution]

Hi @GeorgeColl You can use UNION to add a custom row manually. Something like this:

EVALUATE
UNION(DISTINCT('CAClaims'[Insured Name Simplification]), ROW("Insured Name Simplification", "_No Value Exists"))
ORDER BY 'Insured Name Simplification'

This combines the distinct values with a manually created row _No Value Exists, then sorts the result.