Forum Discussion

heatherl's avatar
heatherl
Frequent Visitor
6 years ago
Solved

Dynamic column concatenating distinct values

Hi,   I have an issue I am working on and have tried a couple different methods but wondering if there is a better way.     I have two tables, REVENUE and WAGES.     These tables are linked t...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi heatherl ,

     

    I would used CONCATENEX, and SELECTEDVALUE

     

    Concatenates the result of an expression evaluated for each row in a table.

    Syntax

    DAXCopy
    CONCATENATEX(<table>, <expression>, [delimiter])

    Parameters

    Term Definition
    tableThe table containing the rows for which the expression will be evaluated.
    expressionThe expression to be evaluated for each row of the table.
    delimiter(optional) A separator to use during concatenation.

    Return value

    A text string.

     

    SELECTEDVALUE

    • 12/09/2018
    • 2 minutes to read
       

    Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

    Syntax

    DAXCopy
    SELECTEDVALUE(<columnName>[, <alternateResult>])

    Parameters

    Term Definition
    columnNameThe name of an existing column, using standard DAX syntax. It cannot be an expression.
    alternateResult(Optional) The value returned when the context for columnName has been filtered down to zero or more than one distinct value. When not provided, the default value is BLANK().

    Return value

    The value when the context for columnName has been filtered down to one distinct value only. Else, alternateResult.

     

  • heatherl's avatar
    heatherl
    6 years ago

    Hi Nathaniel_C , v-lili6-msft ,

    Thanks to both of you for your help. I discovered that if I use my same code creating a measure instead of a custom column, then it works as desired.

     

    EN Code(s) new = calculate(CONCATENATEX(values(WAGES[EN Code]),WAGES[EN Code],","))

     

    So thank you for that!

     

    I have another resulting issue but I will open a separate discussion for it. Thanks again!

     

     

heatherl's avatar
heatherl
Frequent Visitor
6 years ago

Hi Nathaniel_C ,

Thanks for your assistance. Can you explain what the SELECTEDVALUE would be doing here? I'm not understanding what exactly what it does.

 

Based on your CONCATENATEX suggestion I did some more searching and put together this column.

 

test = calculate(CONCATENATEX(values(WAGES[EN Code]),WAGES[EN Code],","))

 

It works at getting the distinct values but it is working the same as my SQL column; when the wages filters are updated, the field still show all of the values even if they are no longer relevant.

 

Thanks

  • heatherl's avatar
    heatherl
    Frequent Visitor
    6 years ago

    Hi Nathaniel_C , v-lili6-msft ,

    Thanks to both of you for your help. I discovered that if I use my same code creating a measure instead of a custom column, then it works as desired.

     

    EN Code(s) new = calculate(CONCATENATEX(values(WAGES[EN Code]),WAGES[EN Code],","))

     

    So thank you for that!

     

    I have another resulting issue but I will open a separate discussion for it. Thanks again!