cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgjones43 Member
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 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9Column 10Required Column
AAAAAAAAAAA
AAABAAAABAA
BAABAAAABAA,B
BBBABBAABBB,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
Super User

Re: Count and rank values across multiple columns

@tgjones43 

 

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 13
Highlighted
adetogni Established Member
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
Super User

Re: Count and rank values across multiple columns

@tgjones43 

 

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 )

 

 

adetogni Established Member
Established Member

Re: Count and rank values across multiple columns

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

tgjones43 Member
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
Super User

Re: Count and rank values across multiple columns

@tgjones43 

 

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 )
tgjones43 Member
Member

Re: Count and rank values across multiple columns

@Zubair_Muhammad  that works perfectly, thank you so much!

tgjones43 Member
Member

Re: Count and rank values across multiple columns

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

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

Re: Count and rank values across multiple columns

Hi @tgjones43 

 

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]
    ,
    ",")