cancel
Showing results for
Did you mean:
tgjones43 Member

## 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 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?

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Count and rank values across multiple columns

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 (
DISTINCT ( temp1 ),
"MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) )
),
[MyCount] >= 3
)
RETURN
CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )
```
13 REPLIES 13
Highlighted
adetogni Established Member

## Re: Count and rank values across multiple columns

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"
) Super User

## Re: Count and rank values across multiple columns

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 =
DISTINCT ( temp1 ),
"MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) )
)
RETURN
CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )
```

adetogni Established Member

## Re: Count and rank values across multiple columns

@Zubair_Muhammad 's solution is much more elegant :-)

tgjones43 Member

## Re: Count and rank values across multiple columns

@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? Super User

## Re: Count and rank values across multiple columns

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 (
DISTINCT ( temp1 ),
"MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) )
),
[MyCount] >= 3
)
RETURN
CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )
```
tgjones43 Member

## Re: Count and rank values across multiple columns

@Zubair_Muhammad  that works perfectly, thank you so much!

tgjones43 Member

## Re: Count and rank values across multiple columns

@Zubair_Muhammad  is it also possible to do this in M? Super User

## Re: Count and rank values across multiple columns

Yes its very much possible.
I will attempt it later tonight and get back to you Super User

## Re: Count and rank values across multiple columns

Here is a custom  column using M which produces same result as DAX

Please see attached file as well

```=Text.Combine(
Table.Sort(
Table.SelectRows(
Table.Group(Table.FromList(Record.ToList(_)),"Column1",
{{"Count", each Table.RowCount(_), type number}})
,
each [Count]>=3)
,
{{"Count",Order.Descending}})[Column1]
,
",")```