Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
Expected output - Jason, Bob
Table: 'PlayerScore'
Name | Score |
Jason | 3 |
Bob | 3 |
Nancy | 1 |
Solved! Go to Solution.
Hey @pyampy,
try this code. For me it worked perfectly.
Measure = CONCATENATEX( Filter('PlayerScore',PlayerScore[Score] = Max(PlayerScore[Score])),PlayerScore[Name] ,", ")
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.
Thank you!
@pyampy try this
_concat =
CONCATENATEX (
FILTER (
tbl,
CALCULATE ( COUNT ( tbl[Name] ), ALLEXCEPT ( tbl, tbl[Score] ) ) > 1
),
tbl[Name],
",",
tbl[Name], ASC
)
Thank you for responding. But this solution doesn't work at 'scale', as the table can have any number of rows.
Name | Score |
Jason | 3 |
Bob | 3 |
Nancy | 1 |
Paul | 5 |
Eric | 3 |
Devon | 5 |
Alex | 5 |
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |