Pivot Table Measure Assistance Needed

[kd712]

Hi!

I am trying to create a measure within my pivot table that lists all of the names of associates. The formula I currently have works:

List= Concatenenatex(Query1, Query1[Last Name], “,”)

However when I run with this measure I get a list that includes the blanks so the return looks like this:

Smith, , Wagner, Cromwell, , , Anderson

What I’m looking for would be more like this:
Smith, Wagner, Cromwell, Anderson

I tried using a TEXTJOIN but it is not being recognized as a function when I create the measure.

Please advise!

 

[Matt Allington]

Then you must have blanks in your source data instead of nulls

try this
List= Concatenenatex(Distinct(Query1[Last Name]), Query1[Last Name], “,”)

im not sure if that will fix it. If not, you may have to wrap it in a calculate

List= Calculate(Concatenenatex(Query1, Query1[Last Name], “,”),Query1[Last Name] <> BLANK())

im not sure what the filter needs to be. It depends on what the blank ”is”.

 

[kd712]

Then you must have blanks in your source data instead of nulls

try this
List= Concatenenatex(Distinct(Query1[Last Name]), Query1[Last Name], “,”)

im not sure if that will fix it. If not, you may have to wrap it in a calculate

List= Calculate(Concatenenatex(Query1, Query1[Last Name], “,”),Query1[Last Name] <> BLANK())

im not sure what the filter needs to be. It depends on what the blank ”is”.

The distinct worked! Thank you!