Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello. I have a table, Test, with two columns:
Locality | Percentage |
A | 1 |
B | 2 |
C | 3 |
D | 4 |
E | -1 |
F | -2 |
I would like to create a measure which lists the positive Localities only in a single string, ordered by Percentage (descending). So in this case it would run "D, C, B and A". I'm using concatenatex and topn to do this.
This is the code I'm using:
It works almost perfectly, but it doesn't give me the answers in order, since TopN doesn't do that. So instead of "D, C, B and A" I get something like "C,B, A and D" - a completely random order. My question is, how can I make sure the Values are concatenated in order? Thanks in advance.
Solved! Go to Solution.
Try using the CONCATENATEX parameters OrderBy_Expression and Order:
Concatenate Measure = CALCULATE (
CONCATENATEX (
TOPN (
COUNTROWS ( DISTINCT ( Test[Locality] ) ) - 1,
test,
Test[Percentage], DESC
),
Test[Locality],
", ",
Test[Percentage],
DESC
) & " and "
& CONCATENATEX ( TOPN ( 1, test, Test[Percentage], ASC ), Test[Locality] ),
Test[Percentage] > 0
)
Proud to be a Super User!
Try using the CONCATENATEX parameters OrderBy_Expression and Order:
Concatenate Measure = CALCULATE (
CONCATENATEX (
TOPN (
COUNTROWS ( DISTINCT ( Test[Locality] ) ) - 1,
test,
Test[Percentage], DESC
),
Test[Locality],
", ",
Test[Percentage],
DESC
) & " and "
& CONCATENATEX ( TOPN ( 1, test, Test[Percentage], ASC ), Test[Locality] ),
Test[Percentage] > 0
)
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
82 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
111 | |
110 | |
72 | |
71 |