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.
Hello,
I'm trying to add a column where I get the first most occurring value across columns in a row along with the second most occurring value, and 3rd most occuring value
ID | Column1 | Column2 | Column3 | Column4 | Column5 |
1234 | Apple | Apple | Orange | Apple | Orange |
1235 | Orange | Orange | Pear | Orange | Orange |
In other words, I'd like a column that would show "Apple" as the first most occurring value and "Orange" as the second most occuring value in the first row. How can I do that?
Solved! Go to Solution.
Hi, @RolandPlanet
I have made a few changes on the measure. Please try the following measure to see if it works.
Result =
var _id = SELECTEDVALUE('Table'[ID])
var tab =
SUMMARIZE(
'Table',
'Table'[ID],
'Table'[Value],
"Count",
COUNTROWS(
FILTER(
'Table',
'Table'[Value] = EARLIER('Table'[Value])
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
RANKX(
FILTER(
tab,
[ID] = _id
),
[Count]
)
)
return
CONCATENATEX(
FILTER(
newtab,
[Rank] = 1
),
[Value],
"-"
)&" "&
CONCATENATEX(
FILTER(
newtab,
[Rank] = 2
),
[Value],
"-"
)&" "&
CONCATENATEX(
FILTER(
newtab,
[Rank] = 3
),
[Value],
"-"
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @RolandPlanet
You may go to Query Editor, go to 'Transform' ribbon, make 'ID' selected and click 'unpivot other columns'. And then you need to click 'Close and Apply'.
You may create a measure as below.
Result =
var _id = SELECTEDVALUE('Table'[ID])
var tab =
SUMMARIZE(
'Table',
'Table'[ID],
'Table'[Value],
"Count",
COUNTROWS(
FILTER(
'Table',
'Table'[Value] = EARLIER('Table'[Value])
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
RANKX(
FILTER(
tab,
[ID] = _id
),
[Count]
)
)
return
CONCATENATEX(
FILTER(
newtab,
[Rank] = 1
),
[Value]
)&" "&
CONCATENATEX(
FILTER(
newtab,
[Rank] = 2
),
[Value]
)&" "&
CONCATENATEX(
FILTER(
newtab,
[Rank] = 3
),
[Value]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is great! However, if two values have the same number of occurences, they get concatenated together as "AppleOrange" for example. Is there a way to prevent this?
Hi, @RolandPlanet
I have made a few changes on the measure. Please try the following measure to see if it works.
Result =
var _id = SELECTEDVALUE('Table'[ID])
var tab =
SUMMARIZE(
'Table',
'Table'[ID],
'Table'[Value],
"Count",
COUNTROWS(
FILTER(
'Table',
'Table'[Value] = EARLIER('Table'[Value])
)
)
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
RANKX(
FILTER(
tab,
[ID] = _id
),
[Count]
)
)
return
CONCATENATEX(
FILTER(
newtab,
[Rank] = 1
),
[Value],
"-"
)&" "&
CONCATENATEX(
FILTER(
newtab,
[Rank] = 2
),
[Value],
"-"
)&" "&
CONCATENATEX(
FILTER(
newtab,
[Rank] = 3
),
[Value],
"-"
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Better you unpivot data
https://radacad.com/pivot-and-unpivot-with-power-bi
Then you can use all expect at ID level to get this answer
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |