Forum Discussion
Solved
Count and rank values across multiple columns
Hi all I have the following data and need a way to create the column at the end (in bold text): Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9 Co...
Sorry I missed that point
Here is the revised code
Column = VAR temp1 = { [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10] } VAR temp2 = FILTER ( ADDCOLUMNS ( DISTINCT ( temp1 ), "MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) ) ), [MyCount] >= 3 ) RETURN CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )
Here is another way.
This is a calculated column
Column =
VAR temp1 = {
[Column 1],
[Column 2],
[Column 3],
[Column 4],
[Column 5],
[Column 6],
[Column 7],
[Column 8],
[Column 9],
[Column 10]
}
VAR temp2 =
ADDCOLUMNS (
DISTINCT ( temp1 ),
"MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) )
)
RETURN
CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )
Zubair_Muhammadthis works nicely to rank the values, but it needs to be only those values that occupy 3 or more columns. At the moment it gives all values that occur in 1 or more columns. Can the code be modified to do this?
Sorry I missed that point
Here is the revised code
Column = VAR temp1 = { [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6], [Column 7], [Column 8], [Column 9], [Column 10] } VAR temp2 = FILTER ( ADDCOLUMNS ( DISTINCT ( temp1 ), "MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) ) ), [MyCount] >= 3 ) RETURN CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )Zubair_Muhammad that works perfectly, thank you so much!
Zubair_Muhammad is it also possible to do this in M?