Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pyampy
Frequent Visitor

Return multiple values from a column based on duplicates

I have a measure that calculates and returns a scalar MAX from a column.

Using this MAX value, I want to return a concatenated string from another column, especially if the table has duplicates.

 

For e.g. there are duplicate scores of 3 in the table 'PlayerScore' below. I want to use the output to be a concatenated string - 'Jason, Bob'. The reason I want it in this format is to use it within the Smart Narrative widget. 

I tried doing it with LOOKUPVALUE but that returns an error as multiple values are returned. 

 

My attempt was:

maxScore = CALCULATE(MAX(PlayerScore[Score])) #this returns 3

 

result = 

LOOKUPVALUE(PlayerScore[Name],PlayerScore[Score],PlayerScore[maxScore]) #this throws an error as multiple values are returned

 

Expected output - Jason, Bob

 

Table: 'PlayerScore'

 

NameScore
Jason3
Bob3
Nancy1
1 ACCEPTED SOLUTION
TOK
Helper II
Helper II

Hey @pyampy,

try this code. For me it worked perfectly.

Measure = CONCATENATEX( Filter('PlayerScore',PlayerScore[Score] = Max(PlayerScore[Score])),PlayerScore[Name] ,", ")

 

View solution in original post

5 REPLIES 5
TOK
Helper II
Helper II

Hey @pyampy,

try this code. For me it worked perfectly.

Measure = CONCATENATEX( Filter('PlayerScore',PlayerScore[Score] = Max(PlayerScore[Score])),PlayerScore[Name] ,", ")

 

I just finished a short tutorial on this topic.

 

pyampy
Frequent Visitor

Thank you!

smpa01
Super User
Super User

@pyampy  try this

_concat =
CONCATENATEX (
    FILTER (
        tbl,
        CALCULATE ( COUNT ( tbl[Name] ), ALLEXCEPT ( tbl, tbl[Score] ) ) > 1
    ),
    tbl[Name],
    ",",
    tbl[Name], ASC
)

 

 

smpa01_0-1642171238079.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
pyampy
Frequent Visitor

Thank you for responding. But this solution doesn't work at 'scale', as the table can have any number of rows. 

 

NameScore
Jason3
Bob3
Nancy1
Paul5
Eric3
Devon5
Alex5

and so on..

 

I want to get all the names with max score (which could be change depending on new data) and return it as a string.  For the above example, now 5 is the max score and the expected output would be 'Paul, Devon, Alex'. 

 

Apologies if I wasn't clear in my original post!

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.