Forum Discussion
Helper IVCount 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 | Column 10 | Required Column |
| A | A | A | A | A | A | A | A | A | A | A |
| A | A | A | B | A | A | A | A | B | A | A |
| B | A | A | B | A | A | A | A | B | A | A,B |
| B | B | B | A | B | B | A | A | B | B | B,A |
the measure needs to present the values which occupy 3 or more of the 10 columns, in order of how many times they occur.
So, for row 1, A is the only answer.
For row 2, A is the only answer as B occurs only twice.
For row 3, A occurs 7 times and B 3 times, so A,B is presented.
For row 4, A occurs 3 times and B 7 times, so B,A is presented.
Is this possible?
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 )
13 Replies
Yes, it's possible, but fairly long.
I'll write a pseudo code:
Colum =
VAR countOfA=IF(table[Column1]="A";1;0)+IF(table[Column2]="A";1;0)+IF(table[Column2]="A";1;0)+...(etc up to 10)
VAR countOfB=IF(table[Column1]="B";1;0)+IF(table[Column2]="B";1;0)+IF(table[Column2]="B";1;0)+...(etc up to 10)
RETURN
SWITCH(TRUE();
countOfA<3;"B";
countOfB<3;"A";
countOfA>countOfB;"A,B";"B,A"
)- Zubair_Muhammad
Community Champion
Here is another way.
This is a calculated columnColumn = 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_Muhammad 's solution is much more elegant :-)
tgjones43Zubair_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?
- Zubair_Muhammad
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 )
